i creating tennis site of course needs display results. ranking need sum scores each player , order them, if need single player results. think slow when player count in system rise. that's why need way cache results , update them when changed(or add sort of timeout when results has updated).
also other requirement being able calculate total scores i.e. have several competitions , need show scores competitions, , each competition separately.
what thought of single table store everything. it's schema be:
ranking_tbl shema
rank_type(could competition, team, player or else)
rank_owner(who owns ranks, can team player ranks - owner team)
rank_item(who ranked, in team example player )
score(actual score rank by)
rank(precached rank, updated when new scores added)
ranking important part of system , used heavily need efficient possible.
question: there better way achieve ranking using table shema?
i think schema work. can see 3 possible solutions use desired functionality.
- cron - using cron job (scheduled task) update rankings on nightly basis mean can bulk processing @ off peak time (2am example). schedule script re-orders players score, assigns them rank , saves database.
- single save recalculation - if inserting scores 1 player @ time possibly @ recalculating ranks after save score. provide excellent date ranks, may have trade off in performance when adding lot of scores.
- multi save recalculation - compile scores csv file contains player id, , score. can write script parse csv, update scores of players. once scores saved can recalculate ranks players.
i prefer 3rd option may have little more overhead in time consuption set up.
hope helps.
