Bad Practice to Store Calculated Data in Database?

Question

Is it bad practice to store calculated data in each row, or is it better to calculate at the application layer with every read from the database.

Storing in the database avoids the need to calculate multiple times, but if an error is made then data needs to be updated rather than just changing the application level calculations.

I think the latter is better, but is there a general rule of thumb?

I need to, for example, calculate total daily nutritional intake of foods. So various portions of energy of foods. I can either calculate the portion energy based on the corresponding food and store the energy of each portion in the portions table OR I can calculate from the join with the corresponding food every time.

You can imagine if you had to calculate yearly averages, monthly averages, daily averages, etc. for a long period of time it could get quite unwieldy.

What about using materialized views that would get recomputed every time old data, say a week or older, gets updated based on a trigger or something along those lines?

Answer 1

If computers were infinitely fast, then, No, you would never store a value that could be calculated from other columns in the database. Storing calculated values is a violation of database normalization.

Examples would include:

• The extended cost on an invoice line that carries price and quantity fields.
• The total cost of an invoice’s lines.
• The elapsed time between two moments.

In the real world, we do sometimes choose to violate normalization. The motivation is usually for performance reasons.

Never violate normal forms without much thought, and hopefully a consultation with another DBA or database developer. And always document thoroughly your decision and its specific motivation.

is there a general rule of thumb?

Yes: Normalize, and test/profile.

Always start with a normalized design. Load with fake data, test performance. Verify any discovered bottleneck is indeed due to your on-the-fly calculations.

Answer 2

An additional consideration: some values are calculated from a continually increasing number of other values. An example: a customer's current balance is calculated by taking adding all their charges and subtracting all their payments. If a customer has been active for years, this can require accessing a large number of rows each time you want to display their balance.

All the same concerns (time/resources to compute on the fly, vs. the recalculation cost if there's a problem) still apply, of course. The difference here is that the cost to compute increases over time.

A system I worked with used a hybrid approach: the customer balance was stored and modified as new charges or payments hit the account, but the value was recalculated from scratch during a nightly process.

Answer 3

It depends. Some values are better to calculate immediately, some values are better to postpone until requested.

Once I had to precalculate distances between each two consecutive GPS points and increase an odometer value stored along with the point data. These spread calculations allowed me to get the distance between any two arbitrary points on the route just by substracting the first odometer value in the range from the last. Calculation of the distance on demand took a lot of time that was unacceptable. But these data aren't critical and if some point was missed odometer/distance values still have acceptable precision.

In my opinion if you can precalculate some data that speeds up some frequent and heavy queries - precalculate them. If you want to precalculate in advance without real necessity - do not waste CPU and IO resources.