Design for 'Total' field in a database

Question

I am trying to find an optimal solution for my Database (MySQL), but I'm stuck over the decision whether or not to store a Total column.

This is the simplified version of my database :

I have a Team table, a Game table and a 'Score' table. Game will have {teamId, scoreId,...} while Score table will have {scoreId, Score,...} (Here ... indicates other columns in the tables).

On the home page I need to show the list of Teams with their scores. Over time the number of Teams will grow to 100s while the list of Score(s) will grow to 100000s. Which is the preferred way:

• Should I sum up the scores and show along with teams every time the page is requested. (I don't want to cache because the scores will keep changing) OR
• Should I have a total_score field in the Team table where I update the total_score of a team every time a new score is added to the Scores table for that group?

Which of the two is a better option or is there any other better way?

Answer 1

I use two guidelines when deciding to store a calculated value. In the best of all worlds, both of these statements will be true.

1) The value must be computationally expensive.
2) The value must have a low probability of changing.

If the cost of calculating the value is very high, but it changes daily, I might consider making a nightly job that updates the value.

Start without the total column and only add it if you start having performance issues.

Answer 2

Calculating sum at request time is better for accuracy but worse for efficiency.

Caching total in a field (dramatically) improves performance of certain queries, but increases code complexity or may show stale data (if you update cached value not at the same time, but via cron job).

It's up to you! :)