best design for a changelog / auditing database table?

Question

i need to create a database table to store different change log/auditing (when something was added,deleted,modified,etc). i dont need to store particularly detailed info, so i was thinking something along the lines of:

• id (for event)
• user that triggered it
• event name
• event description
• timestamp of the event

am i missing something here? obviously i can keep improving the design, although i dont plan on making it complicated (creating other tables for event types or stuff like that is out of the question since it's a complication for my need)

Answer 1

There are many ways to do this. My favorite way is:

0 - Add a mod_user field to your source table (the one you want to log)

1 - Create a log table which contains the fields you want to log, plus a log_datetime and seq_num field. seq_num is the primary key.

2 - Build a trigger on the source table which checks for a change to any monitored field, and inserts the current record into the log table on any change

Now you've got a record of every change and who made it.

Answer 2

You may find some interesting answers here as well.

Answer 3

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.

Answer 4

Some answers and ideas over here

Answer 5

What we have in our table:-

Primary Key Event type (e.g. "UPDATED", "APPROVED") Description ("Frisbar was added to blong") User Id User Id of second authoriser Amount Date/time Generic Id Table Name

The generic id points at a row in the table that was updated and the table name is the name of that table as a string. Not a good DB design, but very usable. All our tables have a single surrogate key column so this works well.

Answer 6

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!