Versioned data strategy
-
I'm working on a system where data integrity is important so rather than just updating records we're always going to insert a new one, quite often there will be links in the system to previous versions so it'd be useful if this data was easily available. Previously this system was being developed by a guy who's now left the company, he's been using GUIDs as Ids and that's something I'm tryin get get away from. The current structure we have is:
Guid Id
Guid RootId
int VersionRootId is the base ID and Id is the primary key, there is also an Audit table where adding an entry for the ID can mark it as deleted, this makes the select statements a bit over complicated since you have to find the maximum version for each RootId then join back to get the actual data and then join again to find any audit entries. I was wondering, what other simpler versioning strategies are there?
-
I'm working on a system where data integrity is important so rather than just updating records we're always going to insert a new one, quite often there will be links in the system to previous versions so it'd be useful if this data was easily available. Previously this system was being developed by a guy who's now left the company, he's been using GUIDs as Ids and that's something I'm tryin get get away from. The current structure we have is:
Guid Id
Guid RootId
int VersionRootId is the base ID and Id is the primary key, there is also an Audit table where adding an entry for the ID can mark it as deleted, this makes the select statements a bit over complicated since you have to find the maximum version for each RootId then join back to get the actual data and then join again to find any audit entries. I was wondering, what other simpler versioning strategies are there?
What I have done is build an audit table:
id int
operation nvarchar(50)
table_name nvarchar(200)
col_name nvarchar(200)
key_col nvarchar(200)
key_val nvarchar(200)
col_val_prior nvarchar(MAX)
col_val_new nvarchar(MAX)
username nvarchar(50)
transaction_dt datetime
batch nvarchar(MAX)id - unique id on this table operation - insert,delete or update table_name - name of table operation pertains to col_name - name of the column operation pertains to key_col - name of the key column operation pertains to key_val - value of the key column operation pertains to col_val_prior - value of col_name column before operation col_val_new - value of col_name column after operation username - name of user running operation transaction_dt - datetime trigger was run batch - guid that uniquely identifies an operation I then created three triggers that I add to all tables to be audited(the triggers cover insert, update and delete operations). This allows me to see all operations on tables I wish to audit.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
I'm working on a system where data integrity is important so rather than just updating records we're always going to insert a new one, quite often there will be links in the system to previous versions so it'd be useful if this data was easily available. Previously this system was being developed by a guy who's now left the company, he's been using GUIDs as Ids and that's something I'm tryin get get away from. The current structure we have is:
Guid Id
Guid RootId
int VersionRootId is the base ID and Id is the primary key, there is also an Audit table where adding an entry for the ID can mark it as deleted, this makes the select statements a bit over complicated since you have to find the maximum version for each RootId then join back to get the actual data and then join again to find any audit entries. I was wondering, what other simpler versioning strategies are there?
Another option is to maintian a heirarchical structure /rootid/child1/child2/, this is what MS heirarchiy structure does, I use a varchar instead of the binary data type but the concept is excellent. You can always trace the changes and also find the root and leaf nodes.
Never underestimate the power of human stupidity RAH