Re: Normalization and Derived Information

From: Bill H <wphaskett_at_THISISMUNGEDatt.net>
Date: Sun, 10 Oct 2004 08:18:31 GMT
Message-ID: <qf6ad.214547$MQ5.94638_at_attbi_s52>


Kenneth:

"Kenneth Downs" <firstinit.lastname_at_lastnameplusfam.net> wrote in message news:mtf9kc.so1.ln_at_mercury.downsfam.net...

[snipped]

> Although I still believe that pre-stored calculated values are faster than
> calc-at-read, I'm going to drop that line for now and try to pursue your
> suggestion of the history table, I want to make sure I fully understand
> what you are suggesting.

There are some excellent reasons for storing calculated values; especially in accounting and other money handling applications. In fact, one might even go so far as to state it is advisable to maintain duplicate data in order to effectuate financial error control.

> So, the history table solves the problem of time as a dimension; it
relieves
> us of the need to violate 3NF, which violation occurs if we copy price
into
> the orders table. So if we go with the history table and preserve 3NF,
> then to get the extended price of the order, we are looking at something
> like this (not claiming SQL is perfect here):
>
> SELECT ordh.ord_date,
> ordd.item_id,ordd.qty,
> ph.price,
> ph.price * ordd.qty as price_extended
> FROM ORDERS ordh
> JOIN ORDERS_DETAIL ordd ON ordh.ord_num = ordd.ord_num
> JOIN PRICE_HISTORY ph ON ordd.item = ph.item
> AND ordh.ord_date >= ph.begin_date
> AND (ordh.ord_date <= ph.end_date
> OR ph.end_date IS NULL)
>
>
> From here my own non-negotiable design requirement is that people such as
> customer IT staff should never have to code the above, they should just be
> able to "SELECT price_extended " from somewhere, so the above can be put
> into a view and so far so good.
>
> Well, hmmmmmm, we (read: you) seem to worked the time dimension in
> satisfactorily, we now have to ask what could screw this up? Well,
> complexity and deeply nested calculations will perform poorly at read,
> while materializing them will delay writes, so since we want fast writes
> (and small transactions) that's another nail in the coffin.

Accounting for the future (figuratively too). Something always goes wrong so one wouldn't want to design and implement myopically. :-) Received on Sun Oct 10 2004 - 10:18:31 CEST

Original text of this message