返回用户最佳分数的结果页面

Question

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.

Answer 1

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.