Current Value Table

As mentioned in the Historical Table discussion, recalculating the final values of an account will be come slower and slower over time. Instead of repeatedly recalculating all of those values every time, we use another table that holds the most recently calculated value. Now we only to make sure the moste recent transactions have been applied to the current values, and then we can retrieve the updated values with a minimum amount of work.

There are multiple ways to update the values in the row. One way is to update it as each transaction is added into the daily table; then, any call to this table will simply return the most current value. This is referred to as eager evaluation.

Another way is to only update the values in the row when it is requested, In this case, we compare the TransactionID of the last transaction used to update the value. If no new transactions have been added, then the values in the row are simply returned. If there are new transactions, then they are used to update the row, and the new values are returned. This method is referred to as deferred or lazy evaluation.

Note, if a row in the historical table is edited, then then it is highly likely that the Current Value for the account will need to be recalculated. If we actually had to start from the beginning of the historical data, this could be quite expensive to do. However, the next two tables in this document describe ways to ameliorate that issue.

Table Columns

There are two columns that should be included in the Current Value Table that will allows us to track the history of changes in the table.

AccountID

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

TransactionID

TransactionID serves two purposes in this table. 1) it lets us know the last transaction applied to the table, thus letting us know which new transactions need to be applied to update the values; and 2) if we return the TransactionID whenever the values are requested, then any system using those values should store the transactionID alongside the results of any work involving those values. This way, if we need to go back to investigate an issue with that system, we are able to precisely recreate the values that were used.

Data

This is not a single field, but instead are all the fields that represent the results of all the transactions applied to an account. For example, if a Historical Table contained transactions that would be used to calculate three different monetary values, then this table should have field for each of those monetary values. If the data is such that multiple rows are needed, then only the minimum number of rows per account should be kept in the table.

Next: Snapshot Table

Copyright © 2017-2018 Adin H. Baber, all rights reserved.