From: Matthew Hagerty <matthew@venux.net>
To: pgsql-interfaces@postgreSQL.org
Subject: [off-topic] Database normalization, can't be done!
Date: Fri, 12 Mar 1999 21:49:41 -0500
Message-ID: <4.1.19990312213418.00a2fac0@mail.venux.net> (raw)
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
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: matthew@venux.net
Subject: Re: [off-topic] Database normalization, can't be done!
In-Reply-To: <4.1.19990312213418.00a2fac0@mail.venux.net>
* 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