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
