July 15, 2008 at 10:25 am
I would like to keep versions of our records as they are changed over time. The application is an internal sales system where the users are creating and editing numerous records each day. The records are typically made up of several INT fields and one or two TEXT fields. I think the use of shadow tables looks to be a good idea because the existing table (along with any existing application) doesn't need to change.
Has anyone used shadow tables or an alternative way of retaining versions of records as the data is modified?
I have also read (http://www.ciselant.de/projects/pg_ci_diff/doc.html) about differential compression. It looks to me that this will need something along the lines of Compiled Code to be running on SQL Server that the update trigger then calls (this is to calculate the delta).
Has anyone made use of a differential technique as well?
I have a development team here that is well capable of creating this from scratch, but I am reluctant to "re-invent the wheel" if there are third party controls or other solutions available. I've had a search, but couldn't find anything.
thanks!
July 15, 2008 at 11:31 am
We had to track versions over time, so we created a version table, put a key for it in every other table and then did nothing but inserts into all the tables with a reference back to the version for that set of data. The trick was, that there wasn't a seperate version for each table, rather a version for a given set distributed across the data. Then it's a simple matter of getting a TOP(1) with an ORDER BY to return the values. Works well. It's been in production for years and scales nicely.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 17, 2008 at 2:34 am
Thanks Grant, that's an interesting idea.
Did you store absolute versions of the records each time? We have a couple of TEXT fields so I think that space may become an issue over time.
Also, was there any requirement for you to present the changes in a User Interface?
cheers!
July 17, 2008 at 5:56 am
When data changed, a given row would be versioned, but not all rows or all tables. Also, the "version" was not a moment-by-moment creation. They would work on a given version of the data for a week or so and we would capture all the data changes within that version over that time. Then it would get published and the next version would start.
We didn't have to worry about BLOB's but I can't say that I would have changed the design if we did. Yes, it would certainly lead to pretty radical storage issues. Another option for text is to add tools like OnBase or Documentum to deal with the document versioning seperately.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply