A Discussion on Storing Calculated Values and Audit Trails
Nov 26 2003, 12:23 PM
Joined: 20-June 02
From: Newcastle, WA
The following is an expansion and synthesis of a discussion in a recent thread on creating audit trails and storing calculated values. It incorporates observations made by several forum members who agreed it should be posted here.
general rule in development discourages storing calculated values in a table.
One example of this would be storing both a person's birth date and age. The age can be calculated or re-calculated at any time from the birth date, which doesn't change, and the current date. There is no need, therefore, to store the person's age. In fact, storing it would create additional maintenance because the age value in the table value would have to be updated each year on the person's birth date.
A more problematic situation occurs when the underlying table values from which the calculated value is derived can change over time. Take, for example, a sales transaction in which the underlying values are "quantity purchased" and "unit price" and the calculated value is "sales price" (i.e., "quantity purchased" x "unit price" = "sales price"). “Quantity purchased” and "unit price" are stored in a transaction table. The general rule against storing calculated values would say that sales price, being a calculated value, should not be stored in the transaction table.
However, storing sales price, in some cases, serves a distinct function, often referred to as an audit trail. In most cases, sales price is not an infinitely calculable value in the sense we most often use because one of its components, unit price, contains an implicit time element. Unit prices can and do change over time. A sales price, therefore, can only be re-calculated accurately if the database provides a way to recover the unit price as of the sales date.
In creating audit trails and in other situations where historical accuracy must be maintained, the definition of a calculated value includes the components stored in the database (quantity and unit price), as well as the time component (unit price as of 11/21/2003, for example), which may or may not be stored in the database.
Therefore, whenever there is a time element in a calculation and an audit trail is required, the database either has to provide a way to re-calculate the value including its time component, or store that value permanently. If the database does allow you to re-calculate the exact value as of the transaction date, then I would argue that the calculated value should not be stored. If it doesn’t allow accurate re-calculation, then the value might be stored.
Obelieve there are three ways to handle situations like this:
• Store the calculated value itself, e.g. “Sales Price”
• Store the “as of date” value for the underlying value which has a time element, e.g. “unit price as of sale date”, in a field in the transaction table.
• Create a History of Values table where each value change is saved as a new record with an ID to relate to the item along with the date of change and the "changed to value", permitting re-calculation of the sales price at any time from the transaction date and the corresponding value in the history of values table.
Because it is always possible to create a solution based on either the second or third options, it seems to me that using the first option—storing the calculated value—is rarely, if ever, a valid approach.
Since the issue is really the need to capture the time element of a transaction in some fashion, and since "Unit Price as of Sales Date", as opposed to " Sales Price", does that, there is no need to store the calculated value.
However, this only applies to situations where one of the underlying values can change over time. Take the example of a donation which also involves both a “quantity” and “unit value”. Say that XYZ Corporation donates 10 boxes of paper to a local school and each box is valued at $25.00. The total “donation value” is $250. That donation value will not change even though XYZ may change the retail price of a box of paper at any time. What matters in this transaction is the unit value stored in the table, not what XYZ currently charges for a box of paper. Therefore, the donation value can be re-calculated at any time from other values stored in the table.
I always ask myself two questions about such values: "Can I get the same value if I re-calculate it next year, or in five years?" If the answer is "Maybe, maybe not," the next question is, "Does it matter if I can't re-calculate it?" If that answer to that question is "Yes, it matters," I think it's appropriate to store an as of date value or, if the history of price changes is important, to add a price change history table.
|Search Top Lo-Fi||1st March 2015 - 11:59 PM|