Take the 2-minute tour ×

I'm building audit tables for my database and need to choose what style to implement. I'm currently considering three options, all of which would be populated using triggers:

  1. A single table with the fields id | table | column | row | old_value | new_value | timestamp | userid. This would track all changes to all tables in a single place and has the benefit of minimizing the number of tables. It does make querying a little difficult, but not impossible.
  2. Multiple tables like #1 except without the table column. This would separate the changes from each table into their own history table.
  3. Multiple tables that mirror the schema of the original tables to track. This would make the triggers a lot easier to write, would make restoration of the data easier if someone wanted to revert to a specific record, but would come at the expense of storage, as every field, even if it hadn't changed, would be duplicated, possibly multiple times. Also, it would make it difficult to know specifically which fields changed from one version to the next.

Each of these three options is do-able, and as far as I can tell there isn't functionality that one offers that is impossible in another. So there must be something I'm not considering or some pattern that is more standard. If it makes any difference, this solution must work for both mysql and sql server (though I can work out the specifics of the code later).

share|improve this question
add comment

3 Answers

up vote 3 down vote accepted

Audit tables are hit very heavily, you do not want only one table for all auditing or you will get blocking.

We do something like number two except we have two tables per table (one that stores the instances of changes and one that stores the actual data. This makes it easy to find all the records stored in amillion record import to a table for instance since they are all inteh same instance. This means we can easily script creating new audit tables as new tables are added.

In the case of second one, I'd suggest writing a proc to restore a specific record so that restoring is easy and you don't have to figure it out each time.

share|improve this answer
HLGEM makes a good point. Breaking it out for performance reasons makes a lot of sense and if you have a lot of read/writes (millions per day) or large batch updates then HLGEM's solution is a good one. However, if your needs aren't nearly that high then option number 1 is a good compromise between performance and maintenance. The databases can handle a lot and overemphasizing optimization can be a mistake depending on the situation. –  theChrisKent Nov 18 '10 at 21:52
oh, so if a user updates 5 fields of a row, then one table gets a new instance of a change, and another table gets 5 new rows of updates, all linked to that instance? That does make restoring easier. –  Bob Baddeley Nov 18 '10 at 21:52
@Bob Baddeley, yes. –  HLGEM Nov 18 '10 at 22:01
Actually since we use the same structure for each audit table, we were able to automate the creation of new audit tables and thus we have very littel need for maintenance. They don't change for each table and we have a job that searches for new tables and creates the audit tables and the triggers to maintain them. –  HLGEM Nov 18 '10 at 22:40
I really like this answer. It seems to handle everything we're trying to accomplish. For your table that has old/new values, how do you deal with datatypes? Do you just have that column as varchar(max) and translate all datatypes to/from text? –  Bob Baddeley Nov 18 '10 at 23:54
show 1 more comment

Not an answer, just further questions: What is the purpose of your audit tables? Why do you want them, need them, or have to have them? How will they be used, what questions will they answer or situations will they address? How frequently or infrequently will they be used? How long must you keep this data available, and how will you purge or archive it after the expiration date?

The two preceding answers [theChrisKen, HLGEM] do not agree, yet--based on what they've worked on before--I'd bet they are both correct. If you contemplate how they will be used and the performance requirements of that usage, thay may help you determine which model is best for your situation.

share|improve this answer
They will be used for 1) notifications of changes to subscribing users (on an instantaneous, daily, weekly, or monthly frequency) 2) displaying a table of changes to a record next to the record itself 3) reverting to a previous revision if the changes are incorrect or inappropriate. The data is part of the record and never gets purged. I can see how both theChrisKen and HLGEM would find their solutions appropriate in different conditions. We're hoping for a solution that scales well, though it won't get beyond a few hundred users (<50 at once) or a few thousand rows to track. –  Bob Baddeley Nov 18 '10 at 22:02
add comment

I would choose number 1 hands down. Number 2 would be hard to maintain if you decide to add additional fields to your tracking and adds very little besides removing the necessity of a WHERE table=? clause. Number 3 is overkill. That's what backups are for.

share|improve this answer
add comment

Your Answer


