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
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?