I have a word game which stores user data in a MySQL database.
I need to display daily, weekly, monthly... leaderboards in the game.
I plan to use a single table for all, structured like:
Columns: userID scoreType dailyScore weeklyScore monthlyScore totalScore
In this structure I'll need to use cron jobs to reset all users' scores with an update query, running daily, weekly, monthly...
Another way I can think of is to use separate tables for each time interval. This way I can truncate the tables using cron jobs. I wonder will this be better in terms of performance?
I'm looking for suggestions on how to structure this kind of a database.