CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
TJ-
TJ-

Reputation: 14373

Design for 'Total' field in a database

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:

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

Upvotes: 4

Views: 1216

Answers (4)

Michael Riley - AKA Gunny
Michael Riley - AKA Gunny

Reputation: 5184

I agree that computed values should not be used except for special situations such as month end snapshots of databases.

I would simply create a view with one column in the view equal to your computed total column. Then you can query the view instead of the base tables.

Upvotes: 1

cadrell0
cadrell0

Reputation: 17337

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.

Upvotes: 2

dejjub-AIS
dejjub-AIS

Reputation: 1541

Depending on how often your scores gets updated and what exactly the "score" means

Case1: Score is a LIVE score

If the "score" is the live score like "runs scored in cricket or baseball" or "score of vollyball match or tabletennis" then I really dont understand the need of showing the "sum" of the "running" scores. However, this may be a requirements also in some cases like showing the total runs scored by a team till now + the runs scored so far in the on going (live) game.

In this case I suggest you another option which is combination of your 1st and 2nd option

Total_score in the team table would be good with slight change in your data model. which is

Add a new column in the scores table called LIVE which will be 0 for a finished match 1 for a live match (and optionally -1 indicating match is about to start but the scores wont get update)

Now union two tables something like

select team_id,sum(total_sore) from (
select team_id,total_score from team
union
select team_id,sum(score) total_score from scores where live = 1 group by team_id)subquery
group by team_id

Case2: Score is just a RESULT

Well just query the db directly (your 1st option) as because the result will be updated only after the game ends and the update infact it will be a new entry in the score table.

If my assumption is correct, the scores get updated only after the game is finished. Moreover the update can be even less often when considered the games played by a team.

Upvotes: 0

Sergio Tulentsev
Sergio Tulentsev

Reputation: 230561

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! :)

Upvotes: 1

Related Questions