CODEWITHSUNDEEP
mysqldatabase-designdatabase-schema
insanebits
insanebits

Reputation: 838

Proper way to create ranking table?

I am creating tennis site which of course needs to display results. Currently to get ranking I need to sum scores for each player and then order them, even if I only need single player results. I think that it will be really slow when player count in system will rise. That's why I need some way to cache results and update them only when something changed(or add some sort of timeout when results has to updated).

Also other requirement would be being able to calculate total scores i.e. I will have several competitions and I will need to show scores for all competitions, and for each competition separately.

What I currently thought of would be single table to store everything. It's schema would be:

ranking_tbl shema

rank_type(could be competition, team, player or something else)

rank_owner(who owns those ranks, can be team player ranks - owner would be team)

rank_item(who is ranked, in team example would be player )

score(actual score to rank by)

rank(precached rank, updated only when new scores added)

Ranking will be important part of my system and used heavily so I need it to be as efficient as possible.

Question: Is there better way to achieve ranking than using my table shema?

Upvotes: 1

Views: 1625

Answers (1)

GaryDevenay
GaryDevenay

Reputation: 2415

I think your schema will work. I can see 3 possible solutions that you could use to get your desired functionality.

  1. Cron - Using a Cron Job (Scheduled Task) to update the rankings on a nightly basis would mean that you can do the bulk processing at an off peak time (2am for example). You would schedule a script that re-orders the players by score, assigns them a rank and saves this to the database.
  2. Single Save Recalculation - If you are inserting scores one player at a time you could possibly look at recalculating the ranks after you save any score. This would provide excellent up to date ranks, but may have some trade off in performance when adding a lot of scores.
  3. Multi Save Recalculation - Compile your scores into a CSV file which contains the player id, and score. You can then write a script to parse your CSV, update the scores of all players. Once the scores are saved you can recalculate the ranks for all players.

I would personally prefer the 3rd option but it may have a little more overhead in time consuption to initially set up.

Hope this helps.

Upvotes: 1

Related Questions