Save calculation result vs Other solution?

[atisz]

Hi,


I know, I have seen here a lot of posts saying it's not a good practice saving the results of calculations in a table. So I had never made something like this.
But now I just simply don't know what else should I do, because I have a value obtained by calculation on a form, that value is good for day1. Day2 I perform the same operations, I get a value, but I have to add it to the value obtained day1, and so on.
What should I do instead saving the sum of obtained daily value with value obtained the day before to the table?

Thanks,
Attila

 

[burrcm]

Not storing a calculation where the data may change is a good and correct policy, however if the calculation is unique and will result in an outcome which never changes, then store it with a clear conscience.

Chris B

 

[atisz]

I'm going to do that

Thanks Chris,


I guess saving the values in this case is quite necessary, because I always most have somewhere the sum of an initially value and all calculated value to which add the new calculated value.

Regards, Attila

 

[missinglinq]

Rules, like pie crusts, are made to be broken! There are numerous situations where, as Chris said, storing calculated values is not only acceptable but mandatory. You simply have to look at the overall situation.

If, for example, you're calculating the square footage of a room, using Length x Width, there's no reason to store the results. Area will always equal Length x Width, so you simply recalculate it anytime you need Area.

But if you're calculating, say, a Sales Tax (or VAT as it's called across the Pond) using Price x TaxRate, where TaxRate is retrieved from table and can change at intervals, storing the results makes sense. If the TaxRate was 2% last year, but 3% this year, and you needed to pull an invoice from last year, recalculating the Sales Tax, using the current TaxRate would give an incorrect result. You could include an effective date for each TaxRate, then each time you ran the calculation check the date then pull the approriate TaxRate, but what have you really done? You've added another stored field (which you were trying to avoid by not storing the calculated value) and you've added complication to your db and wasted time each time the calculation needs to be done. Which is why many developers of accounting apps simply store the results.

Your situation is another example. If today's calculation is based on yesterday's results, storing it is the simplest answer.

 

[atisz]

Thanks

Thanks guys for your replys. I was a little confused before, regarding this question, but now thanks to you, I know when I should or should not use saving values.

Attila

 

[boblarson]

Just my two cents worth here -

The key is knowing what you should and shouldn't do and when and then doing what you need to do to make things work well and efficiently. Normalization is not always efficient, so there are times where denormalization is helpful, or necessary, to do something that is useful to you and your users.

 

[gemma-the-husky]

atisz

but the other thing is, is that the need to store calculated figures sometimes relates to normalisation issues. eg you say you have something changing each day - but then shouldnt you be storing the underlying changes (a spot price or something similar) rather than (or even as well as) dealing with the effect that this causes somewhere else.

The reason everyone says dont store calculation results is that (in general)
a) they should be able to be obtained from the data in the database anyway - and
b) if you store the calculation, and something changes, then the stored calculation may now be incorrect, which means you have to spend al ot of time in your code making sure this cant happen - therefore
c) it only makes sense to store calculations on something "historic" that is often used and wont ever change, such as - eg an overall exam mark, or an invoice total., or
d) when the process of repeating the calculation is slow, and it may be more efficient to store the result. - eg i am sure most accounting systems store the outstanding balance on each sales account - although this can be calculated from the underlying transactions - even worse, they probably also store overall control totals.

this is a bit of a generalisation, and i am sure there are lots of other reasons to save calculations, but hopefully it makes sense - the thing is, storing a calculation result should be a considered decision.

 

[stopher]

atisz

On the theme of what Gemma has said....

The way I read your query is that you are accumulating a value on a daily basis and whilst you are not intersted in the previous history, you are interested in the final sum. However, you may need to consider what happens if say, your pc crashes while entering the current days value. When you get back in, will you know if the sum has been incremented or not. Similarly, could it be possible for more than one person to enter todays value.

If these are issues then storing the history is beneficial because:
- you can review the history
- you can prompt for a missing entry
- you can prevent multiple entries for a day
- you can easily sum
- you negate the need to store the sum

I think you may have arrived at this conclusion in post 3 (but I wasn't sure)

hth
Chris