Audit tables: Each field for table or one table

Question

Everything is fine in my project except with the audit fields. Just insert and update is being audited in our imaginary universe.

I proposed one table like similar to the next examples:

• best design for a changelog / auditing database table?

• Suggestions for implementing audit tables in SQL Server? Just table name, table column, user, action and date.

But my team didn't think the same way, they put a column on each table to track an update or insert time. And when I asked why? they told me that is the way that they keep the track in their work.

In the end I give up and I put every field on each table. Since all the team except me, told me to put that fields.

Example:

Their approach

Table Customer
+-------------+-------------+-----+--------------------------------+-------------+
| Name        | LastName    | ... | LastModification (Audit Field) | User        |
+-------------+-------------+-----+--------------------------------+-------------+
| varchar(30) | varchar(50) | ... | datetime                       | varchar(30) |
+-------------+-------------+-----+--------------------------------+-------------+

My approach

Table Customer
+-------------+-------------+-----+
| Name        | LastName    | ... |
+-------------+-------------+-----+
| varchar(30) | varchar(50) | ... |
+-------------+-------------+-----+

Table Audit
+-----------+------------+--------+------+-------------+
| TableName | TableField | Action | User | DateAndTime |
+-----------+------------+--------+------+-------------+

So the question is:

Which is a better design, one table that keep the history of transactions or one field for each table? (Pro and cons)

Answer 1

Which is a better design, one table that keep the history of transactions or one field for each table? (Pro and cons)

Rather than focus on the 2 choices here's a answer on the 4 approaches I've worked with over the years. Each with its pros and cons.

1. Just three fields

Just add three fields (last action, time_stamp, update_user) to every table and call it a day.

Pros Super easy. Performs well

Cons You can't report on data you don't have, so this structure tells you almost nothing (except for deletes)

2. Clone table

Each table has a copy plus the three audit fields and every time a user changes a record the audit table gets inserted into.

Pros Performs pretty well. Easy to create a row by row history that the user can dig through.

Cons

• Every change to the base table needs a corresponding change to the audit table.
• If the users don't want a row by row history to dig through and they want a report of what exactly changed it can get nasty in a hury. See the answers to How can I write a query to extract individual changes from snapshots of data?

3. History Table only

There's no base table only a history table. This is basically the same as Clone Table except now you have to always get the current record.

Pros Pros of 2 but everything's an insert. Less maintenance then the option 2.

Cons You'll end up losing the maintenance gain because you'll end up maintaining views or you'll be sprinkling get-the-current-record logic all over the place

4. Generic audit table

This table has four columns ( Table*, Column_name, old_value, new_value ) and the three audit fields.

Pros Easy to set up and maintain.

Cons

• Its unintuitive but it takes up a lot of space because your old_value and new_value fields have to be nvarchar(max) or equivalent so it can accept anything that's in your base table.

• Performs poorly on reads and writes.

• Its a pain to set up a row by row history report

• If there's any kind of workflow in the records audit reporting can become non-trivial. For example you get a requirement that users only want to see changes that occur after the status on the records becomes 'approved'. That's hard even in options 2 and 3 but becomes a disaster in the Generic audit approach.

Summary

I prefer #2 the Clone table approach as it seems to work best for me. I've had issues with #1 being insufficient and #4 can be a serious perf nightmare that requires a lot of work to undo.