Simple Table with Auditing

A simple table is one that has one, or perhaps a few rows of data per account in it. Over time, the values may change in a given row, but the number of rows is mostly fixed. For example, one table might show the customer name, address, when the account was opened, and that status of the account (open, closed, suspended, etc). This table would only have one row per account. Another table might list features that the customer has selected, in which case, there might be one row per optional feature enabled.

The simple tables only need to maintain the current state of the values. This way, we maintain the minimal amount of rows per customer, and thus ensure better performance when requesting the data.

Audit Table

Alongside the primary table, we need a second table, generally referred to as an audit table. The difference between the primary table and the audit table is that the audit table never deletes or updates rows. Instead, anytime the primary table is updated, the rows from the primary table will be copied to the audit table, and then the updates will be applied to the primary table. This way we have a history of every single change made to the data. This allows us to do two things quite easily: 1) review the changes that had been made over the history of the account; 2) extract the values that would be in place at any given point in time. Now, if we need to investigate an issue with an account, we can have the investigative tools automatically retrieve the appropriate values from when the issue was occurring.

Table Columns

There are several columns that should be included in the simple table that will allows us to track the history of changes in the table. The list below covers several of them, and their importance to the tracking mechanism.

AccountID

This is a globally unique ID that associates the data with a specific account.

InsertionTime

This tells us when the data was inserted or updated in the table. For the primary table, it simply tells us when the data was last edited. For the audit table, it will let us order the data and select the rows from a specific point in time.

UpdateID

This field will contain the ID of who or what actually updated the data. If the customer was the person updating the info, then we would use the AccountID in this field. If an administrator or customer representative was the person updating the data, then that person's user ID would be used to indicate they were the one to make the changes. If an automated system updated the row, then an identifier specifying the exact system needs to be used. If a batch process updated the row, then a unique identifier specifying the exact batch process should be used.

UpdateReason

UpdateReason tells us why the row was updated. If the customer updated the data, then it might simply have a code indicating the update was done via user action. If an administrator updates the row, then there is a code indicating why the admin did so (customer called, security breach, regulatory violation, etc). Likewise, if a system or batch process updates a value, it too needs to record why; for example: a system marks an account disabled if it falls below a certain amount, would have the code for that, as well as another code indicating the account was automatically re-enabled when the balance rose above the minimum again.

Data

This is not a single field, but whatever fields are necessary for the table to do its job.

Importance of UpdateID and UpdateReason

I am going to reemphasize the importance of the the UpdateID and UpdateReason fields. Many times I have investigated accounts, only to find out that a value in the table was updated "by some system" or "by a batch job"; and then when the question is asked which system or batch job, and no one has the answer. This issue comes about primarily from the fact that the people that originally implemented the table / system / batch job are no longer working at the company. At this point, several people have to spend a lot of time tracking down the details / rediscovering how everything works. This can quickly add up to several hours work from several people. By having these fields in the table, and also having unique IDs for each system or batch job, this issue is largely curtailed.

Next: Historical Table