Ideas on database design for capturing audit trails

Question

How can I maintain a log of the data in my DB?

I have to maintain a log of every change made to each row. That means that I can't allow DELETE and UPDATE to be performed.

How can I keep such a log?

Answer 1

Use "Insert Only Databases"

The basic idea is that you never update or delete data.

Each table has 2 datetime columns from and to.

They start with the value null in each (beginning of time to end of time)

When you need to "change" the row you add a new row, at the same time you update the to in the previous row to Now and the from in the row you are adding to Now.

You read data out of the table via a view that has a where to = null in it.

This method also gives you a picture of the state of your database at any point in time.

EDIT

Just to clarify in response to the comment: The sequence would be given by the primary key of the table, which would be an autoincrement number.

Answer 2

See if my answer to another database logging question contains the information you need. Find it here...

http://stackoverflow.com/questions/1250507/history-tables-pros-cons-and-gotchas-using-triggers-sproc-or-at-application-l/1250541#1250541

Answer 3

Use an "insert only" database, as described by Shiraz Bhaji, but you can use a simpler technique. For each table that you need to maintain audit data for, just have an additional column for Updated Time, defaulting to now. When you make a change to a record, instead of updating, just do an insert with all your data; the UpdatedTime column will get the current time.

Note that this method means you have to break or reconsider your UNIQUE constraints; you can keep a primary key, but the uniqueness becomes a composite of your primary key and your UpdatedTime.

This technique has the advantage of giving you a known range of historical data for each record on the table (each record is valid for a given time if it is the TOP 1 of records WHERE TimeOfInterest > UpdatedTime ORDER BY UpdatedTime DESC) with a low overhead (just a single column on the table). It's also quite amenable to conversion from tables not using this method, with a simple ALTER TABLE to add a single column (which you can name consistently). Then you just need to alter your UNIQUE constraints to use a composite of their current contraints and the UpdatedTime column, and some queries will need to be altered.

Note as well that you can actually avoid converting all of your queries if you create a view of the table that simply returns the most recent entry for each of the records; you end up with a table which maintains historical data transparently, and a view which looks like a regular table without the changelogging.