Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mcdonger
Starting Member
1 Post |
 Posted - 2011-01-11 : 10:24:05
|
| Hi all,I'm creating a database with versioning (the application is a sort of wiki), and I'm struggling to find the best architecture for the database. If it makes and difference, I'm using .NET and LINQ for the application.The solution I'm considering at the moment involves a composite primary key of the ObjectID and the VersionNumber- as a PK this works well as it ensures I have one record for each version of the object. HOWEVER, when I need to insert a new Object, I need it to be automatically assigned a unique ObjectID- but I can't make it an Identity column as I also need to insert records which use existing ObjectIDs. Clearly the problem is that the record does not reflect an Object, but a version of the object. If I were to use this method, how would I go about inserting a new Object with a Unique ID (I'd rather not use uniqueidentifier) in a safe way?Another solution might be to have a separate table to store the archives. This way, the table records each represent a single Object, and the PK can just be the ObjectID, which I can therefore make an Identity column. The archive/ versioning table could then have the ObjectID/VersionNumber Composite PK. Am I correct in suspecting that having the 'archive' data in a separate table should also be better for performance? (Primarily the application will just do reads, writes will be much less frequent and of course reading archives even less so)On writing this I have started to convince myself that the latter option is better, although it is more work.I'd appreciate anyone's opinion, and any pro's and con's that I have missed. |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2011-01-11 : 22:26:13
|
| I went down the route you propose and it's really hard when you get joins, especially tables used to implement many/many. You end up with 2 rows that need versions as well as versions of the in between tables. PK/FK gets wild.An easier way is your second idea. Just create shadow tables with no constraints and have a script to write an insert/update trigger per table to copy data into shadow tables.It's not pure but works well and is easy - certainly less work not more in my experience. |
 |
|
|
|
|
|
|
|
Versioning- Composite PK or Archive Tables?