[AccessD] Programming

Hello Richard,

A generally accepted rule of good data practise is to never store
calculated values in a table, for the exact reason you state; that's
what computers are good at, performing calculations, so there is no
need to store calculated values. Also, storing calculated values
breaks the rules of data storage and normalization.

With that said, there are times when storing calculated values does
make sense. To me at least, others on this list will almost certainly
disagree with me on this.

There will generally be a speed increase for reports etc. from looking
up pre-calculated values from a table rather than looking up the
component values and performing the calculation, especially when the
calculation is complex and/or your report pulls a large number of
records when run. However, the obvious trade-off is increased storage
space requirements and that's just the tip of the iceberg. Another
thing to consider is how many tables the calculation has to hit to be
made. The more joins needed, the more complex everything tends to get
and the slower everything tends to run. This is all greatly
simplifying the question, the "correct" answer, as far as I'm
concerned, depends on your specific situation.

My own personal rule of thumb is to never store a calculated value in
a table unless it's a practical necessity for some specific reason. So
I follow the rule in the vast majority of cases, but break it when it
seems advisable to. For example, I just finished a project where the
report calculations were so complex and hit so many tables that I
decreased the report run time, on a report run twice a day, by over 7
minutes simply by storing a calculated value. That's very significant,
and even though storing a calculated value breaks the "rules" I
consider that a perfectly acceptable trade-off in that case. As it
applies to your case, I wouldn't store a calculated value unless the
speed increase on the report(s) was significant and reporting was a
big factor in the user experience (is the report run 20 times a day or
once a month?).

However, in my case mentioned above, once the value is calculated it
never changes. Another consideration is the need to update stored
calculated values and all that implies. The question, to my mind,
doesn't have a clear-cut yes or no answer.

I think it might be interesting to see what others have to say about
this issue.

Ron



Friday, May 13, 2005,
2:49:53 AM, you wrote:

GR> Hi Group

GR> A general programming question.  Would you ever store calculated values
GR> in a table.  My example is this...

GR> I have a Timesheetline table recording jobstart, jobend times/dates etc.
GR> Do I store jobtotalhours in the table and do I store
GR> Client charge and EmployeePaid amounts.  I would not normally store
GR> calculated values in a table but as these calculations are quite complex
GR> (different charge rates, time periods e.g std and overtime etc)
GR> I thought that once calculated (at the point of data entry) why not
GR> store these values in table (may improved report speed/programming).
GR> Alternativley, isn't this what computers are for ie carrying out complex
GR> calculations so why store value, when it comes to reporting on or
GR> displaying etc simply recaculate.   My leaning now is not to store these
GR> values. Any thoughts?