Audit Logging Strategies

Question

I am trying to decide on the best method for audit logging within my application. The main reason for the log is reporting the sequence of events (changes).

I have a hierarchy of Objects, I need to create reports when something changes on any part of that hierarchy, at a latter date.

I think that I have three options:

1. Have a log for each table and therefore matching the hierarchy of objects then creating a view for the report.
2. Flatten the hierarchy and de-normalise the table, making reporting easier - simple select statement.
3. Have one log table and have a record for each change making reporting harder but more flexible to changes.

I am currently leaning towards option 1.

Answer 1

I have to talk to this subject even though it's old.

It is usually a poor idea to have only one audit table as you will create locking problems in the database as everything hits that table. Use separate audit tables for each table.

It is also a poor idea to have the application do the auditing. Audit must be done at the database level or you risk losing some of the information. Data does not change only from applications in most databases; no one is going to change the prices of all their products one at a time from the user interface when you need a 10% increase to all 10,000,000 of them. Auditing should capture all changes not just some of them. This should be done in a trigger in most databases (SQL server 2008 has a built in auditing function). Some of the worst potential possible changes (employees committing fraud or wanting to maliciously destroy data) also are frequently from places other than the application especially if you allow table level access to users (Which you should not do in any financial database or one that contains personal information). Auditing from the application won't catch this. Developers often forget that in protecting their data, outside sources are not the only threat.

Answer 2

An audit log is basically a chronological list of events that occurred, who performed these events, and what the events were.

I think a flat view would be better as it can be easily ordered and queried. So I'm leaning more towards your option #2/#3.

Include things like the transaction type, the time, the user id, a description of what's changed, and other pertinent information related to your product.

You can also add things to your product over time and you won't need to continually modify your audit log module.