Matthew Hagerty wrote:
> 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.
The data is not redundant if you define the price in the line item table
to be the invoiced price. The invoiced price can be the product price at
the time the invoice was created, or it could be the product price less a
discount, or it could be the product price plus a markup, or it could be
a price a salesman said the buyer could purchase the product at.
The point I trying to make is that the invoiced price may have the same
value as the product price, but it is not the same as the product price.
Therefore it is not redundant data and does not break any normalization
rules.
___________________________________________________________________________
____ | Billy G. Allie | Domain....: Bill.Allie@mug.org
| /| | 7436 Hartwell | Compuserve: 76337,2061
|-/-|----- | Dearborn, MI 48126| MSN.......: B_G_Allie@email.msn.com
|/ |LLIE | (313) 582-1540 |
