Best design for a changelog / auditing database table? [closed]

Question

In the project I'm working on, audit log also started from the very minimalistic design, like the one you described:

event ID
event date/time
event type
user ID
description

The idea was the same: to keep things simple.

However, it quickly became obvious that this minimalistic design was not sufficient. The typical audit was boiling down to questions like this:

Who the heck created/updated/deleted a record 
with ID=X in the table Foo and when?

So, in order to be able to answer such questions quickly (using SQL), we ended up having two additional columns in the audit table

object type (or table name)
object ID

That's when design of our audit log really stabilized (for a few years now).

Of course, the last "improvement" would work only for tables that had surrogate keys. But guess what? All our tables that are worth auditing do have such a key!

We also log old and new values and the column they are from as well as the primary key of the table being audited in an audit detail table. Think what you need the audit table for? Not only do you want to know who made a change and when, but when a bad change happens, you want a fast way to put the data back.

While you are designing, you should write the code to recover data. When you need to recover, it is usually in a hurry, best to already be prepared.

There are several more things you might want to audit, such as table/column names, computer/application from which an update was made, and more.

Now, this depends on how detailed auditing you really need and at what level.

We started building our own trigger-based auditing solution, and we wanted to audit everything and also have a recovery option at hand. This turned out to be too complex, so we ended up reverse engineering the trigger-based, third-party tool ApexSQL Audit to create our own custom solution.

Tips:

• Include before/after values

• Include 3-4 columns for storing the primary key (in case it’s a composite key)

• Store data outside the main database as already suggested by Robert

• Spend a decent amount of time on preparing reports – especially those you might need for recovery

• Plan for storing host/application name – this might come very useful for tracking suspicious activities

There are a lot of interesting answers here and in similar questions. The only things that I can add from personal experience are:

1. Put your audit table in another database. Ideally, you want separation from the original data. If you need to restore your database, you don't really want to restore the audit trail.

2. Denormalize as much as reasonably possible. You want the table to have as few dependencies as possible to the original data. The audit table should be simple and lightning fast to retrieve data from. No fancy joins or lookups across other tables to get to the data.