designing version tables - SQL Server Forums

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.

All Forums

General SQL Server Forums

Database Design and Application Architecture

designing version tables

Author

Topic

rpoojari
Starting Member

6 Posts

Posted - 2008-10-17 : 07:51:06

Hi,
I am working an application where I need to keep different versions for data and I would like to know what is the best way to design it? this is similar to source control, where I can have version of a file that is checked out, latest checked in version and list of history versions.
for each row in the table, I need to store three versions i.e. pending, live and list of history versions. should I store all the three versions in one table? I think this will cause performance issues when querying for live data because the data could be as big as 500,000 rows or even more than that. other option is to store pending, live in one table and history data in another table OR just keep each version in a different table and use a common key across all of them to track the changes?
also when a row gets deleted from the live table how to mark it as deleted in history table? should I add a bit flag column called "IsDeleted" in history table and use that to identiy the deletion?
the requirement for history data is that I should be able to query the history for set of records for any given date range.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-18 : 03:09:18

what you could do is to keep two table one live and other history. you could add action column to history to denote what action each record in history constitutes. All live and pending records will in live table. any changes (update,delete) will be logged onto history by means of trigger created on main table. the Action column denotes what action was performed and you coul also store two date values (from,to) to determine when a particular change happened.(todate will be put as current date and from date the date when this change actually existed in main table using its lastmodified value).