From: Billy G. Allie <Bill.Allie@mug.org>
To: Matthew Hagerty <matthew@venux.net>
Cc: pgsql-interfaces@postgresql.org
Subject: Re: [INTERFACES] [off-topic] Database normalization, can't be done!
Date: Mon, 15 Mar 1999 01:05:52 -0500
Message-ID: <199903150605.BAA11027@bajor.mug.org> (raw)
In-Reply-To: <4.1.19990312213418.00a2fac0@mail.venux.net>
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 |
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: Bill.Allie@mug.org, matthew@venux.net
Subject: Re: [INTERFACES] [off-topic] Database normalization, can't be done!
In-Reply-To: <199903150605.BAA11027@bajor.mug.org>
* 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