Are there any guidelines or rules of thumb to determine when to store aggregate values and when to calculate them on the fly?
For example, suppose I have widgets which users can rate (see schema below). Each time I display a widget I could calculate the average user rating from the
Ratings table. Alternatively I could store the average rating on the
Widget table. This would save me from having to calculate the rating every time I display the widget, but then I'd have to recalculate the average rating each time a user rated a widget.
Ratings Widgets --------- ------- widget_id widget_id user_id name rating avg_rating <--- The column in question