Re: Help with rewriting query

I've done a lot of work with a bookkeeping system where we have such
redundancy built in. The auditors, however, need to be able to generate
lists of the financial transaction detail to support balances. These
reports are among the most demanding in the system. I shudder to think
how unacceptable performance would be without the redundancy.

Also, due to multiple media failures, and backup process problems (on
another database product), a large database was badly mangled. The
redundancies allowed us to reconstruct much data, and to at least
identify what was missing for the rest.

There is, of course, some cost for the redundancy. Up front, someone
needs to code routines to maintain it. It needs to be checked against
the underlying detail periodically, to prevent "drift". And there is a
cost, usually pretty minimal, for the software to do the work.

I strongly recommend that some form of trigger (either native to the
database or, if portability is an issue, within a middle tier framework)
do the work of maintaining the redundant data. If you rely on
application code to maintain it, you can expect that sooner or later it
will get missed.


>>> Tobias Brox <tobias(at)nordicbet(dot)com> 06/11/05 4:59 AM >>>
[
Reminds me about the way the precursor software of our product was made,
whenever it was needed to check the balance of a customer, it was needed
to
scan the whole transaction table and sum up all transactions. This
operation eventually took 3-4 seconds before we released the new
software,
and the customers balance was supposed to show up at several web pages
:-)

By now we have the updated balance both in the customer table and as
"post_balance" in the transaction table. Sometimes redundancy is good.
Much easier to solve inconsistency problems as well :-)