Keeping a history of data changes in database

Question

Every change of data in some row in database should save the previous row data in some kind of history so user can rollback to previous row data state. Is there any good practice for that approach? Tried with DataContract and serializing and deserializing data objects but it becomes little messy with complex objects.

So to be more clear:

1) I am using NHibernate for data access and want to stay out off database dependency (FOr testing using SQL server 2005)

2) What is my intention is to provide data history so every time user can rollback to some previous versions.

An example of usage would be the following:

• I have a news article
• Somebody make some changes to that article
• Main editor see that this news has some typos
• It decides to rollback to previous valid version (untill the newest version is corrected)

I hope I gave you valid info.

Answer 1

What database system are you using? If you're using an ACID (atomicity, consistency, isolation, durability) compliant database, can't you just use the inbuilt rollback facility to go back to a previous transaction?

Answer 2

Tables that store changes when the main table changes are called audit tables. You can do this multiple ways:

• In the database using triggers: I would recommend this approach because then there is no way that data can change without a record being made. You have to account for 3 types of changes when you do this: Add, Delete, Update. Therefore you need trigger functionality that will work on all three.

Also remember that a transaction can modify multiple records at the same time, so you should work with the full set of modified records, not just the last record (as most people belatedly realize they did).

Control will not be returned to the calling program until the trigger execution is completed. So you should keep the code as light and as fast as possible.

• In the middle layer using code: This approach will let you save changes to a different database and possibly take some load off the database. However, a SQL programmer running an UPDATE statement will completely bypass your middle layer and you will not have an audit trail.

Structure of the Audit Table

You will have the following columns:
Autonumber PK, TimeStamp, ActionType + All columns from your original table
and I have done this in the following ways in the past:

Table Structure:
Autonumber PK, TimeStamp, ActionType, TableName, OriginalTableStructureColumns

This structure will mean that you create one audit table per data table saved. The data save and reconstruction is fairly easy to do. I would recommend this approach. Name Value Pair:
Autonumber PK, TimeStamp, ActionType, TableName, PKColumns, ColumnName, OldValue, NewValue

This structure will let you save any table, but you will have to create name value pairs for each column in your trigger. This is very generic, but expensive. You will also need to write some views to recreate the actual rows by unpivoting the data. This gets to be tedious and is not generally the method followed.

Answer 3

You can use triggers for that. Here is one example.

 AutoAudit is a SQL Server (2005, 2008)
 Code-Gen utility that creates Audit
 Trail Triggers with:

     * Created, Modified, and RowVerwsion (incrementing INT) columns to table
     * view to reconstruct deleted rows
     * UDF to reconstruct Row History
     * Schema Audit Trigger to track schema changes
     * Re-code-gens triggers when Alter Table changes the table

http://autoaudit.codeplex.com/

Answer 4

One way is to use a DB which supports this natively, like HBase. I wouldn't normally suggest "Change your DB server to get this one feature," but since you don't specify a DB server in your question I'm presuming you mean this as open-ended, and native support in the server is one of the best implementations of this feature.

Answer 5

Microsoft have introduced new auditing capabilities into SQL Server 2008. Here's an article describing some of the capabilities and design goals which might help in whichever approach you choose.

MSDN - Auditing in SQL Server 2008

Answer 6

Saving serialized data always gets messy in the end, you're right to stay away from that. The best thing to do is to create a parallel "version" table with the same columns as your main table.

For instance, if you have a table named "book", with columns "id", "name", "author", you could add a table named "book_version" with columns "id", "name", "author", "version_date", "version_user"

Each time you insert or update a record on table "book", your application will also insert into "book_version".

Depending on your database system and the way you database access from your application, you may be able to completely automate this (cfr the Versionable plugin in Doctrine)