Another type of table is one that stores a series of transactions. These transactions are then added together to determine a final actual value. For example, accounting software will start with know beginning values, and then apply deposits, withdraws, checks, inter account transfers, etc to determine the final amount of money in the bank account.
Note: Typically when talking about databases, a transaction means an atomic action that is all or nothing. In this case, we are using the bank meaning of transaction; i.e. a check clears the account, a deposit is processed, etc.
The significant difference between a Historical Table, and the Simple Table described earlier is that we never delete any rows from the Historical Table. The more obvious impact of this fact is that, as an account ages, it will accumulate more and more transactions, which in turn will take more time to process when calculating the current values in the account. However, this issue will be alleviated by subsequent table designs discussed later in this document.
A different aspect that needs to be looked at is if a row needs to be edited. For example, a transaction has the wrong amount in it, either due to an error, or system issue, or perhaps a more nefarious reason. In this case, we don't want to merely update the row. What we need to do is "disable" the row, and then insert a replacement row. By doing this, we are able to track all changes made to an account. Several of the fields listed below exist specifically for this reason.
There are several columns that should be included in the Historical 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.
This is a globally unique ID that associates the data with a specific account.
TransactionID is a unique ID that is used to identify a specific transaction. The value in this field should be created such that the later events have a higher value then earlier events. This way, in situations where multiple transactions have the exact same date/time stamp, we would still be able to determine their absolute order by looking at the TransactionID.
TransactionTime is when he transaction takes place, while InsertionTime is when the transaction was inserted into the system. A major reason for these two fields to be different is if a transaction was edited by an account administrator. In this case, the InsertionTime would be when the edit was actually done, and the TransactionTime would be when the transaction "should have" taken place.
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 tells us why the row was updated. This information is extremely important since it will tell auditors why something was changed./p>
Indicated the status of the transaction. Be default it would be a value indicating that the transaction was "good" or "valid". If the transaction was edited, then the status may be changed to something like "replaced", and the replacement transaction will have a status of "good".
This is not a single field, but whatever fields are necessary for the table to do its job.
I am going to reemphasize the importance of the the UpdateID and UpdateReason fields. All the reasons pointed out in the Simple Table section apply here; but this type of table is supposed to represent a complete and accurate view of the accounts history. We need to always know who and why when a piece of history is "rewritten".
Next: Current Value Table