Points system like stackoverflow

Question

Hello,

I am trying to create a point system in my program similar to stack overflow i.e. when the user does some good deed (activity) his/her points are increased. I am wondering what is the best way to go about implementing this in terms of db schema + logic.

I can think of three options:

1. Add an extra field called points in the users table, and everytime a user does something, add it to that field (but this will not be able to show an activity of sorts)
2. Create a function which will run everytime the user does good deed and it calculates from scratch the value and updates the points field
3. Calculate everytime using a function without any points field.

What is the best way to go about this? Thank you for your time.

Answer 1

Personally, I would use the second option to approach this problem.

The first option limits functionality, so I eliminate that right away.

The third option is inefficient in terms of performance - it is likely that you will be fetching that number a lot, and, if your program is anything like stackoverflow, perhaps showing (calculating) that number many times per pageview/action.

To me, the second option is a decent hybrid solution. Normally, I hate having duplicated data in my system (actions and points, rather than one or the other), but in this case, an integer field is a rather small amount of space per user that saves you a lot of time in recalculating the values unnecessarily.

We must, at times, trade data storage space for performance or vice versa, and I would say that #2 is a trade-off that greatly benefits the application.

Answer 2

I would go for 1 and 2 (run in cron on every minute or so).

So that: - extra field would act as a cache to the amount of point. - The function to calc the points could be a single sql query that would recalculate the points for all users at once to gain some speed.

I think that recalculating the field each time when point is recieved would be an overkill.

Answer 3

This depends very much on the number of expected computations you'll face. In fact, SO apparently uses a method which is similar to your 1) approach, for performance reasons I assume.

This also prevents jumps in the numbers if factors change (such as deleted items which awarded points, or here on SO replies which become community wiki, changes in the point rules, external actions such as joining another account here on SO etc.)

If a recalc solution (2) is what you want, you may implement a "smart" caching by clearing the value (setting it to NULL which would mean "dirty") each time a point modification may take place, and re-computing it when it is NULL, using the cache otherwise. You could also (as a self-correcting measure when non-explicit things happened) clear out the values after an hour, a day or whatever you think firs so that a recalc is forced after a certain time, independently of the "dirty" state.

Answer 4

Personally, I'd go with the first option, and add an "Actions" table to keep track of your activity history.

When a user does something good, they get an entry in the "Actions" table, with the action and some point value. The point value can come from another table, or some config file. That same value gets added to the user record.

At any point in time, you could sum up the actions and get the user total, but for performance, simply updating when you add the action record would be simple enough.

Answer 5

How simple is your points system going to be? I reckon some kind of logging / journalling is good so that you can track activity on a daily /weekly/monthly basis across all users