Re: Normalization and Derived Information

From: Bill H <wphaskett_at_THISISMUNGEDatt.net>
Date: Sun, 10 Oct 2004 20:13:39 GMT
Message-ID: <TJgad.217095$MQ5.40639_at_attbi_s52>


Kenneth:

"Kenneth Downs" <firstinit.lastname_at_lastnameplusfam.net> wrote in message news:8nhbkc.5d7.ln_at_mercury.downsfam.net...
> Bill H wrote:

>
> > 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.
> >
>
> Yes, my final fallback is the audit trail. Seeing each column in a chain
of
> calculations gives the auditors the warm-fuzzies, they hate it when you
> tell them, "Oh, the gross profit calc is OK, trust me." If I can make the
> customer happy and guarantee correctness, I'll do it and leave the quetion
> of theory to another day. In this particular response to Mr. Celko
however
> I am pretending that I never want to materialize anything, and trying to
> pursue that through to its final endpoint so that I can discover anything
> I've missed by materializing all these years.

You make an interesting observation. Notice how denormalization has occured: the totals for a list of accounting transactions are kept in another table.

We can derive the total by adding the list of transactions or we can simply look at the total field in another table. The audit trail could be defined as the list of transactions to support the total but the total could be derived from the sum of the transactions. On the other hand, if the total weren't derived in this manner auditing is enhanced.

Bill Received on Sun Oct 10 2004 - 22:13:39 CEST

Original text of this message