I want to make a reward system in my application, where
- Each user will get some points for each task in app e.g.. solving a puzzle, answering an answer etc.
- I want to show the point earners on a leaderboard (like Stack Overflow leaderboard)
- Leaderboard should have the option to show daily, weekly, monthly & all time top point earners.
Currently, i am thinking of a very crude approach:
In the User Table create new column totalpoints which will store all-time points of a user.
Each time User earns point we will increment value in it.
In the User Table create new column dailyPoints which will store daily points earned by a user.
In my app I want to give rewards to top 3 Users with the highest points earned in that day. It will store Points only between 12:15 AM to 11:45 PM. The Buffer time will be used to store find the top 3 winners and after finding top 3 winners, announce winners at 12:00 & reset the counter of each user to 0 and again start adding points in the table after 12:15 AM.
Similarly, for Weekly Leader Board create a new column in User table Weekly_Points & use the same approach like in point 2 i.e. It will store Points only from 12:15 AM Monday to 11:45 PM Sunday.
Similarly, for Monthly Leader Board create a new column in User table Monthly_Points
But the above approach has major drawback i.e. User will not be able to see the ranking of previous days, previous weeks, previous months.
My questions are:
- Is there any better approach which I can use?
- Is this data a better fit for relational or noSQL databases?