Hajo:
With the example of a person's age in the other thread its clearly wrong to
store this as it can always be computed from a person's date of birth, and
will soon become incorrect if it is stored. The date of birth on the other
hand will always be the same for each person. In the language of the
relational model its said to be 'functionally dependent' on the key of a
'people' table.
There are occasions, however, when it is not only right but essential to
store a computed value. Lets assume we have a products table with a column
UnitPrice; we also have a Customers table with a column Discount (as a
fractional value) which determines what discount a customer gets. So if
customer A with a discount of 0.1 (10%) buys product B which has a unit price
of 10.00 GBP they pay 9.00 GBP for it, i.e. the price paid is computed as
UnitPrice *(1 - Discount), and this is what appears on the invoice.
Now if we simply calculated this in a computed column in a query or a
computed control in a form or report this would be fine so long as the
UnitPrice of product B stays at 10.00 GBP and customer A's discount stays at
10%. But they are a particularly good customer so we increase their discount
to 12.5%. Also, because of the current economic recession we drop the price
of product B to 9.00 GBP. So if customer A purchases another product B he
now pays 7.88 GBP, i.e. 9 * (1 - 0.125).
If we go back to the invoice for his first purchase however, that now also
shows that be paid 7.88 GBP whereas he in fact paid 9.00 GBP; the first
invoice is incorrect, our accounts are in a mess and the auditor is not
happy! The solution of course is that in this case we do store the computed
value in each invoice by computing the value and then assigning the it to a
field in an InvoiceDetails table. There is no redundancy involved because
the price paid is functionally dependent on the key of the InvoiceDetails
table whereas the unit price is functionally dependent on the key of the
Products table and the Discount is functionally dependent on the key of the
Customers table. You'll find an example of this in the Orders Subform in the
sample Northwind database where code in the ProductID control's AfterUpdate
event procedure looks up the unit price and assigns it to a field in the
OrderDetails table. In the Northwind example the discount is then applied to
the stored unit price in the form's underlying query and the final price
shown in a computed ExtendedPrice column, but the principle is the same – the
columns containing stored values are functionally dependent solely on the key
of the table.
Functional dependency as described above should be the only criterion for
storing a computed value, not the fact that you need to use the value in
different contexts such as forms or reports. If the computed values in those
cases are redundant, i.e. not functionally dependent solely on the whole of
the key of the table in which the column is located, then they should be
computed each time. Often doing it in a computed column in a query will be
the best way, as the query can then be used as the basis for whatever forms
and/or reports are necessary.
Ken Sheridan
Stafford, England
