CODEWITHSUNDEEP
phpmysql
Jeff Brown
Jeff Brown

Reputation: 82

Return page of results around User's best score

I currently have a MySQL table setup that saves only the User's best score. If they get a better score the previous score is updated. I also want to have a 24 Hour leaderboard that only shows scores posted within the last... 24 hours.

Two options come to mind:

1) Have 2 separate tables for each leaderboard. A "Best Score" leaderboard that only saves 1 score per user and a "24 Hour" leaderboard that saves 1 score per user and is time stamped. This means every time a score is submitted it needs to be sent to 2 tables instead of 1 but the data footprint will be minimal compared to the next option.

2) Have 1 table that allows duplicate scores to be posted per-user, time stamps the scores and then simply queries the huge table of scores with the data I need (best score overall, scores within 24 hours, etc).

Any ideas/suggestions? This is the first time I've dealt with PHP/MySQL so I'm not sure what the best approach would be design/efficiency-wise.

Upvotes: 0

Views: 69

Answers (2)

sgroh
sgroh

Reputation: 354

I suggest have only one table that holds the best scores.

Some disadvantages of have two tables for your problem are:

  • To present information perhaps you will need a join between tables (less performant that a normalized table)
  • The code should be less readable and less maintainable.
  • To maintain some consistency, you should work with transaction to update both.

Using only one table you should create indexes on the fields that you will use in your queries to present the 24 leaderboard. I.e: Candidates indexes will be the timestamps that will be used on the WHERE clause.

Upvotes: 0

Lock
Lock

Reputation: 5522

For a normalized database, you should only have the 1 table for all scores. You should only insert, not update or delete.

Then, if you want to find the maximum score for instance, you could apply a MAX on the query.

If you want to find scores within the last 24 hours, you could add this into your WHERE clause.

Upvotes: 2

Related Questions