audit table structure

Question

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

Answer 1

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.

Answer 2

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.