Keeping a history of changes to your data can be invaluable if something goes wrong. They may also help to analyse the data when things are working well, but you need to know when and how a change occurred.
Here are a few things you should consider including in your application to ensure it’s easy to track changes.
Created By
It might seem obvious for something like a blog post. But what about a new product in a store? Who added that product? Keep a record of the user id and you’ll soon be able to find out.
Created Date
Again, it seems so simple, yet it’s easy to miss for non-transactional records. Being able to see a user’s registration date or the date of an order gives plenty of scope for reporting on a variety of date-based metrics. And of course, it’s helpful for seeing when something was added to the system.
Updated By, Updated Date
When did a record last change? Who made the change? This is useful to know but it can start to clog up your database tables, so there’s a better way…
Full Auditing Tables
This includes all of the above fields, along with two very important fields: old value and new value. This approach can either be used to log changes to specific fields, or you could use a serialized array to store an entire record. However, this will be harder to search, and your tables will get very large. You could simplify this approach by using triggers.
Anticipate That Request
Without adding massive amounts of extra functionality to your application, it’s worth considering the reporting requests that might be required from your application in the future. How easy will it be for you to provide an export of the necessary data? Will you be able to build reports for admins to download without having to ask you first?
It’s a lot easier to build a site with auditing in mind, than to go back and apply auditing to an existing system – especially if you work without a great deal of documentation, or the original documentation was not kept up to date. Expect to do plenty of digging in the code.
What’s your approach to auditing? Share your views in the comments below.
Photo by Tony the Misfit (Back Soon)
