Question Storing Calculated values

[pr2-eugin]

Hello there, I came across some post where I read "It is not good/correct to store calculated values in a table" (and also someone told me so, but did not explain why !!). I want to know what this sentence imply, and what are the disdavantages of soing so.??

 

[pbaldy]

See if this helps:

http://allenbrowne.com/casu-14.html

 

[pr2-eugin]

Thank you Paul. It was insightful. Still I have a small concern about that.

I have a field called as 'NextInstallment' (however this field is primarly for information and is not used in any query or report, infact it changes everytime the record is viewed (Form_Current event)) which obtains the next installment date based on the 'StartDate' and 'Term' of the policy. Will this be classified as a 'Calculated field'?

 

[Pat Hartman]

I can't tell from your description why the value is changing. If NextInstallment is calculated based on StartDate and Term, there is no reason to store the calculated value and in fact as you saw from the article, many reasons not to. If there is another variable such as when you look at the record, then it may make sense to store the calculated value. We don't have enough information.

 

[sparks80]

Hi,

I think this would count as a calculated field - except you are performing the calculation on a form instead of a query.

If you were to store a value based on the [Start Date] and [Term] in a table, this would have to be explicitly modified each time either of these fields was changed.

As the calculation is relatively simple it makes sense to perform this on-the-fly.

 

[gemma-the-husky]

the problem with storing calculated values (ie information that could be derived from other information in the database) is that there is always the possibility of loss of integrity.

the underlying data can change, and the calculated value does not get updated - so the values are inconsistent

so the chief reasons for storing a calculated value are maybe

that it is an important value. Say an invoice total, or a VAT/Sales Tax amount, that will need to be referred to, and will not change. You could compute these by adding the invoice lines each time, but often a designer will still store the calculated figures

secondly, information that would otherwise take a long time to evaluate. Say totals of a large domain. You often see sales ledger/receivables system where the account header stores an outstanding balance. This could be calculated by adding all the transactions, but it is still common to see the total on the account header.

the downside is that you then have to be very careful over the integrity of the calculated fields, and probably need some sort of extra "checking" module to use from time to time, to verify the accuracy of the calculated fields

therefore a decision to use a calculated field is something that the designer should make in a considered way.

 

[pr2-eugin]

Thank you Pat and Sparks for that.

I now understand that there is absolutely no necessity for me to store it, I can use it in an unbound textbox.