[AccessD] Programming

Hi Andy et all

There is really only one reason to store calculated or accumulated
values, materialized views, and the like: speed.
Anything else represents poor design.
If parameters change, store the date/time for the change, and you can
recalculate at any time later.
The general approach to this is "temporal database" design.

Allow me to quote an old posting from Dec. 2001:

<quote>

> -----Original Message-----
> From: Gustav Brock [mailto:gustav at cactus.dk] 

[snip]

> What may confuse this discussion is that to some degree this 
> is more a matter of
> business rules than database "do and don't". For legacy 
> applications clients and
> invoices cannot be deleted "if you like to" and for some 
> registration and
> statistics not even an address change can be done without 
> keeping a history of
> previous addresses etc.

[snip]

This has been a very interesting thread and I have been considering
how
I could/should have implemented it in my system.  But Gustav's comment
about tracking previous addresses connected with a concept that I
encountered for the first time this morning:  temporal databases.  I'm
still trying to wrap my mind around it, but here are the links I have
at
this time:

Take the "What is Temporal Data?" at http://www.timeconsult.com/ 

Also, http://www.cs.arizona.edu/people/rts/timecenter/timecenter.html 

(watch for wrap)

DonaldB

</quote>

The links are still valid and should be able to keep you busy for a
couple of days!

/gustav


>>> andy at minstersystems.co.uk 05/13 4:18 pm >>>
Funnily enough I was thinking of a payroll system as a good example of
this.
'Net Pay' is a calculated figure but who in their right mind would
write a
payroll system which did not store it?

"Would you please reprint my last December's payslip?"
"Couldn't possibly do that the tax caculation has changed since then."

Ok, it's an extreme instance but it illustrates the point that it'd be
pretty rash to adopt the position of "never store a calculated value".

--
Andy Lacey
http://www.minstersystems.co.uk 



--------- Original Message --------
From: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
To: 'Access Developers discussion and problem solving'
<accessd at databaseadvisors.com>
Subject: RE: [AccessD] Programming
Date: 13/05/05 13:37

>
> I agree with most of what the others say.
>
> Sometimes it is very expensive (resource wise) to calculate a value. 
I
> store the values in this situation.
>
> Another is when you have to store a value as it was calculated at
that time
> (unless you store the values used to make the calculation).  We have
an app
> that calculates deducts on payroll.  There are many places in the
system
> where the parameters used to calculate the value can change.  But
they do
> not want the value to be recalculated unless they manually cause a
> recalculation.  For that reason, the value is stored in the
database.
>
> Bobby
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Griffiths,
> Richard
> Sent: Friday, May 13, 2005 3:50 AM
> To: AccessD
> Subject: [AccessD] Programming
>
>
> Hi Group
>
> A general programming question.  Would you ever store calculated
values in a
> table.  My example is this...
>
> I have a Timesheetline table recording jobstart, jobend times/dates
etc. Do
> I store jobtotalhours in the table and do I store Client charge and
> EmployeePaid amounts.  I would not normally store calculated values
in a
> table but as these calculations are quite complex (different charge
rates,
> time periods e.g std and overtime etc) I thought that once calculated
(at
> the point of data entry) why not store these values in table (may
improved
> report speed/programming). Alternativley, isn't this what computers
are for
> ie carrying out complex calculations so why store value, when it
comes to
> reporting on or
> displaying etc simply recaculate.   My leaning now is not to store
these
> values. Any thoughts?
>
> Richard