I have (wiil have) several linked tables on SQL Server. A 'one' and about five 'many'.
I need to keep a 'version number for each record and it's related records.
Basically it's all in 'spec' info and each year these are revised and a version Date is given and recoreded for that set of spec (i.e. each record and it's related records).
If a change is made (which can be at any time during the year) then a new version date is given and recorded with the current date.
BUT...
The previous version spec and previous version date MUST be maintained and made accessable to the end user.
The user must be able to access a particular Version ID/Date and view ALL the spec for that Version.
What is the best way to go about this?
I haven't really got into the nooks and crannies of SQL beyound tables, viewws, SPs and UDFs. Haven't dabbled with triggers or any date stamp features.
The way I was going to do it was to create a 'Version Table' that holds a Version ID (PK) and Version Date. A Version ID (FK) would then live in each table and this would be the main master 'one table'.
But this would involve creating several duplicate records for everytime a change is made in a related record.
Is there a better way without a Version table and use SQL Server's built in features?
Many Thanks
I need to keep a 'version number for each record and it's related records.
Basically it's all in 'spec' info and each year these are revised and a version Date is given and recoreded for that set of spec (i.e. each record and it's related records).
If a change is made (which can be at any time during the year) then a new version date is given and recorded with the current date.
BUT...
The previous version spec and previous version date MUST be maintained and made accessable to the end user.
The user must be able to access a particular Version ID/Date and view ALL the spec for that Version.
What is the best way to go about this?
I haven't really got into the nooks and crannies of SQL beyound tables, viewws, SPs and UDFs. Haven't dabbled with triggers or any date stamp features.
The way I was going to do it was to create a 'Version Table' that holds a Version ID (PK) and Version Date. A Version ID (FK) would then live in each table and this would be the main master 'one table'.
But this would involve creating several duplicate records for everytime a change is made in a related record.
Is there a better way without a Version table and use SQL Server's built in features?
Many Thanks
