Web Application Database Design: Audit Trail . Tracking changes to column data value changes.

A departure from my typical gadget, general computing posts. Today, I'm going to discuss database auditing trail. Or, if you are searching from the web, "How to keep track of database column value changes."

So what is an Audit Trail (also commonly called an Audit Log)? It is basically a record keeping of changes in a database. For example, if a user updates a record, an audit log should record who made the changes and what the changes were. For some industries, this is an absolute requirement. There are many different ways to implement it and in this blog, I am going to share a technique I use for web applications.

Here is an example of why you need to have an audit trail or a history table of your database changes. I developed a B2B web app that sends materials to various retail stores. Often, the address changes and shipments are often lost. Usually, the client like to blame the developers for the missed shipments which could be very costly. However, with an an audit trail, the developer can proved that the wrong address was the result of bad input on behalf of the client.

A common method to audit changes in a database is to create a trigger that monitors INSERTS, UPDATE, DELETES. Here is an good example fro this website,simple-talk.

Unfortunately, most web applications usually only have a single user login for the app itself. Users are often stored in a user table. If your users are managed by a web front end with a user table, triggers won't work for this type of scenario.

Another common mistake is to make a duplicate history table which mimics the table you want to log. If you have multiple tables, you end making multiple history tables. To me, this is bad design.

My method is to make a singular history table of all the changes. Yes, there is overhead as there are often two transactions. One for the actual UPDATE/INSERT and one for the actual logging. However,  I think the overhead is well worth it in most circumstances. This can be done as a trigger or a concurrent action for your application.

Here is an example schema of an audit trail log table in MySQL. It is a single table that can account for any database table you plan to use. It is an "Amorphous" table; meaning it is consolidated and links by reference to whatever you need to log. In my example,the Audit Log is a table called "AuditLog" for this discussion.

Let me explain some of the entity columns.
ParentID is the key the record you are logging.
ParentObject is the table you are referencing.
CreatedById is the user ID of the user and CreatedDate should be obvious to when the log was created.
FieldName refers to the table column.
DataType refers to the TYPE of the column: VARCHAR, DECIMAL, INT, etc.
OldValue refers to the original data and NewValue is the new data.
If your data is a larger, you can use OldText/NewText for large changes like TEXT.
Notes are internal notes you can flag such as deletes, inserts,etc.

Here is an example of a record that was updated. The id of that original record was 4420 and we know it is the Stores table that was updated based on the ParentObject.
The Field was the Address. As you can see, the old address was 15426 Emerald.. and it was updated by userid 52. You can simply do a JOIN to get more info on the user.

This works well for my needs. To restore a record, you can do an UPDATE with a JOIN.

UPDATE $ParentObject s SET s.`$FieldName` = A.OldValue JOIN AuditLog A ON s.Id = A.ParentId WHERE A.id = 179;

Here is another example. If you wanted to see the name changes of a particular user "124" from a "user" table, you can do a simple select like this: SELECT * FROM AuditLog WHERE ParentId = 124 AND ParentObject ='user'

id	ParentObject	FieldName	ParentId	CreateDate	CreatedById	OldValue	NewValue
1241	user	name	124	2005-03-01	1	J Smith	Jon Smith
1654	user	name	124	2009-08-25	2	Jon Smith	Jonah Smith
2547	user	name	124	2010-01-10	1	Jonah Smith	Jonathan Smith
3645	user	name	124	2010-07-21	4	Jonathan Smith	Jonathan Smith Sr.

So if you are using something like PHP and MySQL, you can log database changes and restore updates easily. This is a simple way to keep track of database changes.