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

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

event IDevent date/timeevent typeuser IDdescription

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