[CLUG-tech] PostgreSQL question (slightly off-topic)

Hendrik Visage hvjunk at gmail.com
Tue Mar 6 15:53:23 SAST 2007


On 3/6/07, Charles Oertel <charles at finebushpeople.net> wrote:
> Hi Chavoux
>
> Chavoux Luyt wrote:
> > When we learned database design at varsity one of the principles was
> > to never store anything in the database that can be calculated from
> > the existing fields:
>
> Great in principle, but like normalization, should be broken if
> conditions warrant it.
>
> Here are patterns where storing the calculated value is indicated:
>
> 1) The calculation is CPU intensive and/or complicated, and
>
> 2) the input data is seldom changed, and/or by only a few applications,
> while the result is accessed frequently and/or by diverse applications.
>
>
> There may be more...

In short: Always have the application etc. be flexible enough and
modular in it's calling structures to allow for such changes.

This is the part where OO principles can be very useful, ie.
 function TotalCost(recnum){
 return recnum.labour  + recnum.material;
}

which is then easily enough changed to
 return recnum.total_cost;

when the need arises.

Again the idea behind those CompSci principles is that things should
be as flexible as possible, and then to rely on one field to be up to
date when you suddenly get a distributed setup etc. etc. could make
things go hairy.

A further point to remember is that in the future the labour_cost and
material_cost could get split into multiple field for multiple types
or even multipe line items etc.

But the only times I'll look into the static versus calculated methods
is when the CPU are 100% utilized and the disks are idling, because
mostly the problem is the disks are 100% utilized/busy while the
CPU(s) are idling...


-- 
Hendrik Visage


More information about the clug-tech mailing list