1

I want to make a reward system in my application, where

  1. Each user will get some points for each task in app e.g.. solving a puzzle, answering an answer etc.
  2. I want to show the point earners on a leaderboard (like Stack Overflow leaderboard)
  3. Leaderboard should have the option to show daily, weekly, monthly & all time top point earners.

Currently, i am thinking of a very crude approach:

  1. 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.

  2. 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.

  3. 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.

  4. 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:

  1. Is there any better approach which I can use?
  2. Is this data a better fit for relational or noSQL databases?

1 Answer 1

4

If you want to be able to access or reproduce any historic scores, you should make a table PointsEarned where each record has

  • a timestamp
  • a value (the points earned for completing one task)
  • a foreign key reference to user table

This table will allow you easily to sum up the total points, or the points for any other time interval you like at run time, and there will be no immediate need to add any additional columns for fixed, predefined times like "last week", "last month", "last day between some arbitrary hours" to your database.

Of course, the more points your users get, the more time it may take to determine the total number of points, and if that value is needed often, it might be a good idea to add something like a (redundant!) "total points" column to your users table for caching purposes. But I would recommend to measure or observe first if that is really needed.

4
  • You are right as the size increases Points Earned table will become very huge. For example, in 1 days 1 million user gets 1 point each for 10 different tasks that means 10 million records in a day. Jan 6, 2018 at 19:11
  • "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." - Is Resetting score good if historical data not required. Jan 6, 2018 at 19:13
  • @AshishRathee: do you really have a user base of 1 million active users, each one completing ~10 tasks a per day? If not, don't overthink this. And even if you have such a user base, adding an "total points" is an operation per user, so for this operation it is pretty irrelevant how many users you have.
    – Doc Brown
    Jan 6, 2018 at 23:14
  • ... and if number of records will really become a problem, you could aggregate them. For example, add all points per day per user together to one record for that day, for each day which lies back more than a week.
    – Doc Brown
    Jan 6, 2018 at 23:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.