Optimal way to store leaderboards by month I have a leaderboard table of users for current month. I need to save all leaderboards by months and display current/old leaderboard on site. Should i create a new table when every month stars? Is this a good approach? In two years i will have 24 additional tables in my database which is really confusing me.
What is optimal way to do this?
Why not just add a MonthOf and YearOf column (likely as integer types) to your Leaderboard table? Then you'll only need one table to store all the leaderboards. Whenever you need to show the Leaderboard for a particular month and year, you just add those fields as predicates in your WHERE clause of your query. E.g. WHERE MonthOf = 1 AND YearOf = 2021 to get the Leaderboard for month of January 2021.
As Vérace mentioned, knowing which database system and version you're using, your Leaderboard table's current structure, and some sample data, would be helpful. But in any case, the above generic solution should generally work, and you should ensure the MonthOf and YearOf fields are part of an index for optimal execution of your queries.