In my last post I wrote about the importance of the database to the long-term performance and functionality of the application. In this post, I will talk about one particular piece of functionality that should be given consideration prior to the design of the database.

This aspect, which is very often forgotten during the initial analysis and design, is change auditing and data history. This is where every time a data element is changed information regarding that data element is captured such as who changed it, when it was changed and what the old data element was prior to the change. One reason that this is often overlooked is the fact that the team is so focused on ensuring that all of the functionality they have envisioned is captured, which is their first priority. The other is that it is common human nature to believe that everyone who will be entering data will make those entries correctly and there won’t be a need to identify information relating to those changes.

However, for one of a variety of reasons such as a need to change legitimately incorrectly entered data, a disgruntled employee, a need to know what date an entry was made or a need to see which employee or customer made a data entry, it is inevitable that at some point in time, usually sooner than expected, someone will ask the question “When was that data entered and who entered it?” or even “What was the previous value before we changed it?”.

It always costs significantly less to incorporate any auditing and history functionality into the application from the beginning than to retrofit it later. Since capturing this information is pervasive and essentially touches almost every aspect of the application, the retrofitting cost can, and will, be quite significant.

However, the conflicting problem is that adding this functionality, not just to the database, but to the front-end capturing of the changes is expensive in its own right, even at the beginning of the process in terms of analysis, design, development and testing of the application. Not only does it take manpower to perform the analysis, development and testing tasks, but it also diverts project management focus. All of these things add up to a more expensive application, a longer production time and later delivery date.

So, eventually tradeoffs need to be made. These are not easy or straightforward decisions. However, it is better to hold these discussions early on so that a detailed conversation can be held where a complete list of the pros and cons can be presented and everyone is aware of the ramifications both for and against, of adding auditing and change history functionality to the application from the very beginning. Obviously, adding it from the start is the best approach, but unfortunately, we don’t live in a perfect world where we have unlimited money, resources and time, so this becomes one area where fingers can be crossed and cutbacks made in order to get an application into production.