Matthew Hagerty <matthew@venux.net> writes:
> Okay, so lookup the price and store it in the Line Item table, problem
> fixed. Nope, that brakes a 3rd or 4th normal, I think. You can have no
> redundant data.
If you want to apply normalization theory to a database that includes
historical data, then you have to recognize time as an explicit
variable. In other words, the scenario you gave with a new record in
the Products table every time a product's price changes is the
academically "correct" approach. Actually, since most of the other
info about a Product probably doesn't change when the price changes,
you'd really want a separate table Prices, keyed by product ID and
timespan of validity. Then you'd use the product ID and date from
an invoice record to look up the applicable price.
If you want to be able to answer the question "What has the list price
for this product been in the past, as a function of time?" then you
might actually want to keep such a table. If you only need to be able
to answer the question "What did we charge on invoice NNN?" then I think
storing the price in the invoice record is the correct approach.
(As D'Arcy points out, there are other factors such as customer
discounts that make it even more reasonable to treat the price charged
in a particular transaction as a fact unique to that transaction.)
The thing to recognize is that a historical price charged in some
past transaction is *not* the "same data" as the current price for
the same product, and so storing the value separately is not
denormalization. It would be denormalization if you wanted the past
invoices to change when you updated the current price --- but obviously
you do not.
Normalization theory is just a tool to ensure that when you want to
change "one fact", you only have to change it in one place in your
database. What is "one fact" is for you to define. In this
application, past invoice prices are clearly not the same fact as
current prices.
regards, tom lane
