I copy the unit cost and unit price into the invoice. This makes sure the
invoice does not change even though the inventory could change. May not be
normalized but this is necessary. The invoice still references the
Inventory so I can get the inventory name without storing it in the invoice.
I don't care if name of the item changes, I do care if the cost or price of
the item changes.
-----Original Message-----
From: Matthew Hagerty [SMTP:matthew@venux.net]
Sent: Friday, March 12, 1999 7:50 PM
To: pgsql-interfaces@postgreSQL.org
Subject: [INTERFACES] [off-topic] Database normalization,
can't be done!
Greetings,
Sorry for the off-topic, I am probably responsible for more
off-topic than
anyone else, but I don't know a better bunch of database people
anywhere :)
So tell me, how do I set up a database where I have to rely on
certain
records to not change, but they have to change? Let me explain.
Invoices
table has a unique ID. Line Items table stores the Invoice ID
(foreign
key). Now, the Line Item table also stores a product ID, used to
lookup
the price in the Products table. So, you create an invoice. 1
month later
there is a price change on some products. Now all invoices are
inaccurate,
they do not reflect what was charged at the time of the invoice,
only what
that invoice would be today.
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. I learned this little problem the hard way, I
created a
system based on looking everything up, very normalized. To fix it,
any
price changes resulted in a new product being created with the new
price
and the old product being voided and only used to build history
invoices.
This wound up costing more space that simply storing the price in
the Line
Item table.
So, someone please tell me how to deal with this kind of problem. I
am
doing a similar system in terms of looking up data and storing data
in
separate tables. But now instead of just needing to store a price,
I need
to store almost every field from one table into another to keep the
history
records accurate. Please help. Any insight, suggestions, insults,
etc.
would be greatly appreciated.
Thank you,
Matthew
