What's the best way to implement a points system?

Ask us anything How do I?
1

I have a “points log” database table where I award points to users. Users can use their points to unlock certain functions in the app.

How do I accurately show the number of points each user has on their dashboard?

So far, I have thought of 2 ideas:

The first is to have a “points” column in the user database table. And then I will update this value every time they are awarded points / if they spend their points. The problem with this is that if something messes up somewhere, then this total points column becomes inaccurate permanently.

The second idea is for me to query the points log and sum up all the points that they have been awarded / spent. The problem with this method is that Xano has to go through the whole points log before summing everything up. Not sure if this will affect performance if I have to fetch / sum everything every time the page loads?

What’s the best way to do so?

2 Likes
2

That’s an usual problem: if you calculate the current status from all previous events it’s slower but you know the calculation is right, or if you change the calculation logic it gets auto updated, if you calculate it at every event and save the result it’s faster but you need to be sure that the calculation is correct, and if you change your logic in the future you need to update everything.

If you think you will not iterate over the calculation logic you should be all right with saving the result in the database, and you can test that is correct.
An alternative is to store some snapshot of the calculated every X time (like every week/month or every time the user reaches a new level), fetch the remaining events from the snapshot to the current time and calculate the final value.

1 Like