Audit tables: Each field for table or one table

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.