How would I use an audit trail to display which fields have ever been edited?

Question

For a project I am working on, I have been asked to create an audit trail of all changes that have been made to records. This is the first time I have had to create an audit trail, so I have been doing a lot of research on the subject.

The application will be developed in PHP/MSSQL, and will be low-traffic.

From my reading, I have pretty much decided to have an audit table and use triggers to record the changes in the table.

The two requirements for display in the application are as follows:

1. Be able to see a log of all changes made to a field (I pretty much know how to do this)

2. Be able to see, when viewing a record in the application, an indicator next to any field in the record that has ever been changed (and possibly other info like the date of the last change).

Item #2 is the one that is currently giving me grief. Without doing a separate query for each field (or a very long nested query that will take ages to execute), does anyone have suggestions for an optimal way to do this? (I have thought of adding an extra "ModifiedFlag" field for each field in the table, that will act as boolean indicator if the field has ever been edited, but that seems like a lot of overhead.

Answer 1

I would treat the audit information separately from the actual domain information as much as possible.

Requirement #1: I think you will create additional audit tables to record the changes. Eric suggestion is a good one, creating the audit information using triggers in the SQL database. This way your application needs not be aware of the audit logic.

If your database does not support triggers, then perhaps you are using some kind of persistence or database layer. This would also be a good place to put this kind of logic, as again you minimize any dependencies between normal application code and the audit code.

Requirement #2: As for showing the indicators: I would not create boolean fields in the table that stores the actual. (This would cause all sorts of dependencies to exist between your normal application code and your audit trail code.)

I would try to let the code responsible for displaying the form also be responsible for showing audit data on field level. This will cause query overhead, but that is the cost for displaying this extra layer of information. Perhaps you can minimize the database overhead by adding metadata to the audit information that allows for easy retrieval.

Some big Enterprisy application that I maintain uses roughly the following structure:

• A change header table corresponding to a change of a record in a table.

Fields:

changeId, changeTable, changedPrimaryKey, userName, dateTime

- A change field table corresponding to a field that is changed.

Fields:

changeId, changeField, oldValue, NewValue

Sample content:

Change Header:

'1', 'BooksTable', '1852860138', 'AdamsD', '2009-07-01 15:30'

Change Item:

'1', 'Title', 'The Hitchhiker's Guide to the Gaxaly', 'The Hitchhiker's Guide to the Galaxy'
'1', 'Author', 'Duglas Adasm', 'Douglas Adams'

This structure allows both easy viewing of audit trails as well as easy retrieval for showing the desired indicators. One query (inner join in the Header and Items table) would be enough to retrieve all information to show in a single form. (Or even a table when you have a list of shown Id's)