February 7th, 2007
Advanced Topics in Database Audit Trails: Part 1
The post on this blog that gets the most search engine traffic is Leaving an Audit Trail In Your Database. I explain several techniques that allow you to store information in the database about how and when records are being changed, with the most comprehensive and effective solution being to create history tables. The obvious benefit is when data is inadvertently changed or deleted, you can track down what happened as well as restore the correct information. That’s not the only good use for an audit trail, though. Providing read only access of the audit information to users, gives accurate and up-to-date history reports of the data.
Consider the following tables.
Customer
Customer History
Each row in Customer History represents a change that was made to a record in the Customer table. The history table contains information that is potentially very valuable to the users of the system you are creating. Why not make it available to them? Using the data above, you could create a screen that displayed the system history for John Q. Doe.
| Date | Event | By |
|---|---|---|
| 3/26/06 | New customer: John Doe (317-555-5678) | Andy |
| 6/9/06 | Phone Number changed from 317-555-5678 to 317-555-1234 | Andy |
| 6/15/06 | Name changed from John Doe to John Q. Doe | Jim |
| 7/28/06 | Customer removed from system | Andy |
If a user of your system wanted to know any of John’s previous phone numbers, this customer history would show them. The alternative would be your user calling or emailing you and asking you to check for old phone numbers. There’s a time delay while they wait for you to get the message, check the database, and respond.
That’s even assuming that your user knows how to get in touch with you and is technically savvy enough to realize that an old phone number may be in the database somewhere. It’s probably more likely that it would never even occur to your user to ask.
Which, of course, means a history screen is your opportunity to enjoy one of the most satisfying aspect of being a system designer. You get to surprise your user with a feature they didn’t even know they wanted. But once they start using it, they can’t remember how they got by without it.
In part 2 of this series I share how to give your audit trail an extra layer of security.
