Take the 2-minute tour ×

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:

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.


Their approach

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

My approach

Table Customer
| Name        | LastName    | ... |

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)

share|improve this question
In their solution, do they only maintain a single copy of the row with the last UPDATE time, or do they maintain multiple versions of the row? –  Larry Lustig Nov 2 '11 at 3:06
@LarryLustig : A copy? they add the column to the table. Not copies at all. Just a last modified field. –  razpeitia Nov 2 '11 at 4:24
The "their" approach provides semantic auditing on a per-row basis, whereas the "your" approach only does this on a per-table basis. –  Sam Aug 7 '13 at 1:00
add comment

1 Answer

up vote 15 down vote accepted


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.


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.


  • 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.


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.

share|improve this answer
Thanks for answering. I really appreciate all the detail in the answer. –  razpeitia Nov 2 '11 at 7:11
Hi, you said you prefer second approach. From the question the AuditTable has column TableField which was changed/affected. What if I change more than one column in a table? Should I add more rows to the AuditTable? Is there another approach of doing so? Can the audit table stay independent or should I make a relation with other tables by some way? –  RandomUser May 9 at 4:43
add comment

Your Answer


