Is it every ok to store a calculated value in a table?

[EternalMyrtle]

We all know the rule that calculated fields should not be stored in tables but is it ever ok to tweak this rule?

Please hear me out...

My database is for an international architectural firm. The US projects use the imperial system (feet) but every other country uses the metric system. These days the breakdown of our projects is probably about 40/60 US/International.

Since we aren't always using one as the primary unit of measurement, I have both in my table (i.e. HeightinFeet, HeightinMeters, etc). In my projects form, I use code to disable the feet/meters controls depending upon the country the project is located in and to calculate the value of the other measurement and store it in the table.

Is this unacceptable? What would be a better alternative?

 

[namliam]

My database is for an international architectural firm. The US projects use the imperial system (feet) but every other country uses the metric system.

Not quite true, but yeah....

Is this unacceptable? What would be a better alternative?

IMHO, yes unacceptable, I would have probably made only the normal columns Height, width, length, weight, etc then make one additional column for the "country type" using inches/pounds/stones/etc vs cm/kilo's/etc....

 

[gemma-the-husky]

well all you need to do really is store the appropriate calculation method, and calculate it when ever you need

However, I think the justification for storing calculated values requires 2 main features fold. First to enable you to get results quicker, that might otherwise take a long time to calculate. Second, where the calculations will not change over time.

couple of examples
a monthly payroll payment, which can de derived from an annual (or monthly) pay rate table, but even so, is generally stored separately

an invoice total obtained by adding together the values of lines on the invoice, but it is still convenient to have the value as an immediate figure, without the calculation overhead.


Where it becomes dangerous to store calculated values, is when the underlying data might change, and the calculated value becomes inconsistent with the underlying data.

 

[pr2-eugin]

The straight forward answer would be 'No', the reason is basically because (a) You have to do a bit of coding, (b) The values have to be updated every time.

Simple and easy solution would be to register the Company with a field denoting the Unit (1 - Metric or 2 - Imperial). Then store the actual data, eg 1.8 or 5.9, so in queries based on the option display the unit as 1.8 m or 5.9 Feet. This is my opinion.

Although, at times its "okay" to break some rules, this comes with understanding the risks and headaches attached with such practice.

 

[spikepl]

As pr2 said, roughly. If you were selling goods in different countries, would you have a different column for each currency? Or a column of values and a currency indicator?

The "don't store" -hysteria however deos need to be taken with a sound portion of scepticism - there are times where storing is justifiable - and thus needs to be justified on a case-by-case basis.

 

[Frothingslosh]

An example of when I might save a calculated value: Sales tax charged. Sales tax rates do change on rare occasions, and if your sales system just calculates the value and doesn't save the sales tax charged, a change in tax rate would make all your pre-change records show the wrong amount charged. That could make the IRS just a bit annoyed.

 

[Mile-O]

An example of when I might save a calculated value: Sales tax charged. Sales tax rates do change on rare occasions, and if your sales system just calculates the value and doesn't save the sales tax charged, a change in tax rate would make all your pre-change records show the wrong amount charged. That could make the IRS just a bit annoyed.

In a tax table you could have, aside from a Tax Type field (there may be more than one) you could also have Start & End Date fields, and then you can query the sales tax where the date of sale was between these two dates.

 

[Frothingslosh]

In a tax table you could have, aside from a Tax Type field (there may be more than one) you could also have Start & End Date fields, and then you can query the sales tax where the date of sale was between these two dates.

Also true. On the flip side, there are times where the wrong tax may have been inadvertently charged, but you still need to correct things but keep the record of what was charged.

Aren't sales and purchasing systems fun?

It's definitely a case by case basis, with the recommendation almost always being to NOT save calculated data. I just brought up the sales tax as a 'might happen here' scenario.

 

[ButtonMoon]

We all know the rule that calculated fields should not be stored in tables but is it ever ok to tweak this rule?

I wouldn't even call that much of a "rule". It's very common and perfectly reasonable to store calculated values. One reason for doing that is as an optimisation to avoid repeated calculations. Another reason might be to permit archiving of certain data while preserving the summary results derived from it.

Since we aren't always using one as the primary unit of measurement, I have both in my table (i.e. HeightinFeet, HeightinMeters, etc). In my projects form, I use code to disable the feet/meters controls depending upon the country the project is located in and to calculate the value of the other measurement and store it in the table.

Is this unacceptable? What would be a better alternative?

Have you considered having one measurement attribute with another unit of measure attribute ("ft", "m"). That way you preserve the full precision and the default units originally specified by the architect (surely important to know) without any awkward recurring decimals or loss of precision that would be caused by converting on-the-fly. You could still display the measurement in whatever units you choose at runtime.

 

[EternalMyrtle]

Thank you for all your replies.

We need to use both units of measurement interchangeably and at a moment's notice. So, even if a project was executed in the US using feet as a unit of measurement, if we are going after a project in Japan, we will put the meters down in our proposals. because we need easy access to both values i decided to just save both values in the table.

I haven't had any problems with this set up but I do understand that it probably isn't ideal. Changing it to what Paul and Spikepl suggest will go on my to do list.