From: Tom Lane <tgl@sss.pgh.pa.us>
To: Matthew Hagerty <matthew@venux.net>
Cc: pgsql-interfaces@postgreSQL.org
Subject: Re: [INTERFACES] [off-topic] Database normalization, can't be done!
Date: Sat, 13 Mar 1999 12:11:44 -0500
Message-ID: <25175.921345104@sss.pgh.pa.us> (raw)
In-Reply-To: <4.1.19990312213418.00a2fac0@mail.venux.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: tgl@sss.pgh.pa.us, matthew@venux.net
Subject: Re: [INTERFACES] [off-topic] Database normalization, can't be done!
In-Reply-To: <25175.921345104@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox