How to implement historical version control?

Question

We are in the early stages of creating a large C # MVC2 application (we also use Sharp and Nhibernate architecture as part of the ecosystem) on SQL 2008 R2, and one of the requirements is that all versions of database rows are available for a given period of history.

We played with a layout idea like:

id (PK)
RecordId
VERSIONID

and each edited record result in a new record created with the same recordId, and with the extension versionId. Recording will then be done using something along the SELECT ... WHERE recordId = X AND versionId = MAX (versionId) lines

Snapshots on each transaction will not work (too much? And not accessible from the application easily).

But we are curious what other implementations have been tried with success or potential problems with our proposal.

Answer 1

You seem to be referring to a temporary table. Three approaches:

Valid state table : adding two "timestamp" columns (for example, of type DATETIME ) indicating when the row became valid, and indicating when the row ceased to be valid, the time span is the period of validity of the row

Transaction status table : associates each row with the period of time during which this row was present in the controlled table, thereby allowing you to restore the state of the controlled table at any previous point in time.

Bitemporal table : captures both the actual time and the transaction time, simultaneously records the history of the enterprise, and also captures the sequence of changes in the record of this history.

Source: Developing Time-Oriented SQL Applications (Richard T Snodgrass) .

Answer 2

We have a system developed by our database administrators that works as a trigger for updating / deleting. There is a secondary table that almost reflects the table being checked (in addition to some other details, such as transaction time, login used to perform the update, server, etc.). At any time, when someone makes changes, he is registered in the audit version of the table using a trigger. This annoys the need to constantly update the audit trigger at any time when the scheme changes, but c'est la vie.

It’s good that applications do not have to be associated with this audit at all ... therefore it saves the number of concepts for the application code low (er).

This works and works on tables where the number of transactions is tens of thousands per day. Of course, your mileage may vary depending on the amount of your server and the nature of your data, but it works for us :-)

Answer 3

Instead of the versionId version, which requires you to join yourself in order to get the maximum version, I would introduce a validFrom - validTo pair. This requires that you update the current validTo (end) record when you insert a new version of the row, but makes it easy to select the current data with where @now >= validFrom and @now < validTo or data at any historical time.

You can either have these historical records in a separate table or not. If you want to have only one table that includes all versions of rows that are better served and can be used, you might want to take a look at the SQL Server Partitioning section, which allows you to separate recent history from old history and optimize your search on him.

Answer 4

I had a similar problem when I needed to check every change on a set of tables.

The biggest problem we encountered was performance when trying to use only NHibernate functions to manage massive inserts and updates (because the user interface required them). Therefore, our solution was to use TRIGGERS to check all the information about the tables, and the response time is an incredible comparison of any solution that we could come up with at that time.

If someone asks me how to do this, I would say that triggers are a way of checking your data.

Answer 5

If you have SQL 2008 Enterprise, and depending on your intention, you can save on data change (CDC).

It really depends on whether you keep previous versions for audit purposes or for some other reason.

Answer 6

My company also uses a trigger approach for auditing and history. In addition to the main table in the corporate warehouse, each significant table has an audit table in a separate database. This audit table has 1 row for each transaction. Some of our tables also have a historical version in the third database. The audit database is intended solely for troubleshooting and troubleshooting, but for data analysis it is complex and not required. Our history database is optimized to respond very accurately to time requests. All of this is 100% written using the .net tool that I wrote, so when we change the schema or add new tables to the story, we just return the script to the affected triggers.