Re: Normalization and Derived Information

Tony:

"Tony Andrews" <andrewst_at_onetel.com> wrote in message news:1097583889.867434.43460_at_c13g2000cwb.googlegroups.com...
> 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.
>
> I never quite "get" the accounting and audit mind-set. In this example
> we have 2 choices:
> 1) Materialize the total on demand: "the total value of orders is
> $1249.43"
> 2) Maintain the total as duplicate data, so that we can display on
> demand: "the total value of orders is $1249.43"

Accounting has been around forever. People have been tracking their money since money was invented. The very concept of dual-entry accounting violates relational normalization rules. However, it does serve a very useful purpose.

Money is a commodity with a lot of gravity...id attracts people. Computers are a disaster waiting to happen. Combine the two and one needs a way to track transactions because s#%$ happens. For years, accounting has addressed this risk. But computers introduce additional risk which the software needs to address.

So, as has been pointed out, denormalization can be good (but don't spread this around). :-)

> Now in case (1) the total is sure to be right, whereas in (2) it could
> be wrong because our stored total has somehow got out of sync with the
> detail data. Yet this is somehow seen as a "good thing", because if we
> add up all the details and find that that DO agree with the stored
> total (as they should), we somehow have more confidence in the total.
> Why? I am sure I must be missing something.

A common method to test transactions is to derive the totals in different ways. This is reminiscent of having two different people counting cash (not together). There are many accounting procedures that employ this concept: the person reconciling bank accounts never deposits to or draws from the accounts. e.g. the person reconciling the account never makes deposits or writes checks.

We've discussed this before, when referring to theory being put in practice. Almost always theory gets adjusted to account for that which wasn't originally accounted for. Also, we've discussed trying to make programming easier to work with so those with the business knowledge can program.

But technologists generally disagree with these observations and we end up with Laconic2's list of standard responses: get lost, you're stupid, I'm stupid, I'm perfect, you're not, and what have we here - an idiot! :-)

Excuse my rambling but my daughter's on my lap and we have to go to swimming.

Bill Received on Thu Oct 14 2004 - 00:59:43 CEST