At my current workplace, there is a group of programmers working on our next-generation app for in-house work. Their work includes the design of the enterprise DB, which is where I have a question for people.
In the current design, they are implementing in-table row versioning. In this implementation, when a row is modified, instead of actually modifying the existing row, the original row is flagged as 'deleted', and a new row is added. A pair of Date fields indicate the date/times that the row was valid from - to (labeled as Date added and date modified), with no IsDeleted flag indicating the current row.
Each table will have an identity field, and a second surrogate 'Identity' field for identifying the rows related to one entity. The table's PK (and a clustered index) will be based on the second 'Identity' field.
Personally, I believe that tables like this will become increasingly problematic as time goes on, growing ridiculously large and slow to query, and difficult to run applications against. However, I need some actual information on this:
1) Has anyone ever actually considered an implementation like this in SQL Server?
2) If so, what were your thoughts on this?
3) If you went to Implementation, what were the results?
4) Are there any existing theoretial works that support this methodology? If so, what are they?
This is one of the most intelligent groups I know on SQL Server questions, and I would really like an assist on answering this.
Points will be split, or supplemental points will be added in extra questions. Thanks!
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
As far as I know, this type of implementation is being used for data warehousing applications (OLAP). In data warehouseing applications, records/rows are not directly updated but simply flagged as deleted by an expiration date and a new record is inserted. Given that it is primarily used for data warehousing applications, it is not recommended for on-line transaction processing (OLTP) applications because, as you've said, it is going to be slow when querying.
"Personally, I believe that tables like this will become increasingly problematic as time goes on, growing ridiculously large and slow to query, and difficult to run applications against."
I absolutely concur. With all due respect to your designer, IMHO it would appear to me that their real world experience would appear to be limited.
I have seen such an implementation on 2 occasions (both government sponsored and bother went horribly wrong and developemnt costs and time where extreme), on both occasions I was contracted in to scrap it and "do it properly". I like people who develop schemas like this as they are creating future contract work for me :)
I have come across a handfull of books in my time endorsing such an aproach, but these are theoretical and are so remote from the real world that I have only found them usefull as kindling for my fire :)
I have also seen a failed attempt to implement something very similar. These were the few problems that they were facing:
1) Unnecessary reference of those columns in each and every query in each and every application. Many times people forgot to apply the conditions and thus there were bugs (very easy to fix but still there were bugs).
2) Since the rows were never deleted (physically), the foreign key enforcement for delete/update was non-existent. This created serious problems both during development and during production. Obviously they end up having several orphan rows in child tables where the master table row was deleted (not physically) without doing the same in child table. This in was the biggest problem.
3) Any table that had more than few thousand rows, had to include these columns in the indexes.
4) The need to do this was not application or database specific but just that in case the user deleted incorrect data, they could recover. There was no application looking at those deleted (not physically) rows. So hardly ever that deleted data was used (by DBA) to restore or for audit purpose.
If the need is audit, I would recommend moving the updated/deleted rows to audit tables in the same database or a different audit database. If the need is some application specific, we need to know and may be there is a better alternative.
One interesting little point, i would mention if you feel like challanging the designers on it... is on the deleted flag. I assume being a flag this is a BIT field. You cant add bit fields to indexes, thus lets say in a simple case you had say a table called PERSON and a column called personID, leats say you flag that as deleted and crreate another record with the same person ID but not flagged as deleted as a "subsitute".
You cant define PersonID as a primary key as it is not unique and you cant define the primary key as the personid and the deleted flag, as that may also not be unique and a bit field cant be in an index. So what you have got is a table who's logical primary key cant be enfoced other than through triggers, then youll have to impose your relationships through triggers, and foreign key constraints, etc. . gulp.... this will as fast as using carrier pigeons to as your transport layer for online gaming :)
Business Accounts
Answer for Membership
by: amit_gPosted on 2005-06-08 at 12:17:21ID: 14173731
What is the basic purpose of versioning? Audit or real need for the application that uses this database?