Thread: RE: [INTERFACES] [off-topic] Database normalization, can't be don e!

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

Thus spake Michael Davis
> 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

In fact, it is normalized.  You just have to realize that things that
look similar and have the same name could still be different attributes.
In this case, you have two distinct attributes, the current, theoretical
price if you were to sell something and the actual price charged to a
client.  Of course, these two are related in a conversational sense and
certainly one is derived from the other but item price is only one of
the components of invoice price.  Another component (there could be
more such as client code, etc.) would be time.

In any case, ultimately your database should be designed to get the job
done, not to be the basis of a thesis on database design.  I'm not
saying that you shouldn't worry about normalizing your database but
like many things it is important to understand the theory so that you know
when to break the rules.  As long as you know what you are doing and it
is a conscious decision, a database schema could be 98% correct and
still be a good design.  The test is, is it the best way to do the job.

--
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.