Storing vs calculating aggregate values

It depends. Pre-calculating aggregate values places a larger load on writes, deriving them makes reads more difficult

If you are frequently accessing a derived value, pre-calculation is a valid de-normalization step. However, in this instance, I recommend using a Materialized View (a view, written to disk, linked by trigger to the parent tables). The materialized view is designed to store frequently asked but tedious-to-derive data, and is useful for high numbers of writes and low numbers of reads.

In a high-write, high-read scenario, consider having a task in the background which mimics the effects of a materialized view, but in less than real-time. This will present a "good enough" average while preserving write and read performance.

In no circumstances, should you treat the derived column like a "normal" column: make sure the data presented in the Widgets "view" is present elsewhere in the table, such that the entire tuple can be derived by whatever processes you emplace. This question is also strongly database (and database-version) specific, so I recommend performance testing of the aggregate (with appropriate indexes) against a normal-sized data set and the materialized view.

How often you need to calculate/display the values relative to how often the underlying numbers are changed/updated.

So, if you have a website with 10k daily hits that's displaying a value that's only going to change once an hour, I'd calculate it when the underlying values change (could be a database trigger, whatever).

If you have a tool to go and look at stats, where the stats are changing by the second, but you only three people have access, and they only look at it a couple of times a day, I'd be more likely to calculate it on the fly. (unless, it takes a couple of minutes to calculate that having had stale data in the first place isn't a big deal ... and my boss tells me to just generate the thing from cron every hour, so he doesn't have to wait when he wants to look at it.)

Use StaleWidgets table as a queue of "invalid" (to be recalculated) widgets. Use other thread (asynchronous) task that can recalculate these values. Period or moment of recalculations depends on system requirements:

• just on read,
• at the end of month,
• for some user at the start of day
• ...