Ideas on database design for capturing audit trails

database database-design audit
Greens picture Greens · Jun 26, 2009 · Viewed 26k times · Source

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

Shiraz Bhaiji picture Shiraz Bhaiji · Jun 27, 2009

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.

Related questions

Best design for a changelog / auditing database table?

I need to create a database table to store different changelog/auditing (when something was added, deleted, modified, etc). I don't need to store particularly detailed info, so I was thinking something along the lines of: id (for the event) …

Read More
Can I have multiple primary keys in a single table?

Can I have multiple primary keys in a single table?

Read More
Database, Table and Column Naming Conventions?

Whenever I design a database, I always wonder if there is a best way of naming an item in my database. Quite often I ask myself the following questions: Should table names be plural? Should column names be singular? Should …

Read More