Best way to store User points system in database

I am currently working on a small app where users accrue points by completing tasks. What I would like is some advise on the best way to design my database tables. In some ways the system is similar to SO, users complete tasks (on SO thy answer questions) and then their point a balance is updated.

I currently have a users table that stores the usual user information, but don't know how to take it forward.

Am I best to have a points table (user has many points) and then add u开发者_运维技巧ser_id and points amount; then when querying a users points, pull out all points with a given user_id and sum them?

Is there a more efficient way of doing this?

Thanks,

Jack

---

If you're only concerned with point total, then as Oded suggested, just include the field directly in the User table. However, if you need to know HOW those points were acquired, perhaps you can have a table for point transactions. This transaction table can then have a foreign key to the User table's user_id field and various fields like Action, Points, Date.

Am I best to have a points table (user has many points)

Why would a user have many points? As in, points associated with various categories? As Oded also alluded to, this comes down to the meaning of these points and what kind of information you want to know about them (also in relation to the users who acquired them or had an impact on them, i.e. voting an answer down reduces the posting user's points).

---

The extra points table is a fair approach as it allows to track the reasons for earned points. Depending on the number of users, point transactions, and the used server, I would consider holding a few commonly needed aggregates: Most commonly the users total points.

To insert new point records in the points table you could use a stored procedure that not only records the points in the points table but also increases the points aggregate for each user. This aggregate could be held in a separate column in the users table (as suggested by Oded).

As your design advances there might appear new useful aggregates that could be precomputed.

But to keep things simple: Only use these aggregates if absolutely necessary, as for example computing the total points of a user takes too long. For a small system this really should not be a problem to calculate these totals on the fly.

---

If points don't have any meaning without a user and are a one-to-one relationship with it, I would put the field directly on the user table.

If, however, you are recording different types of points per user, I would create a points_type lookup and a points table keyed to the user and points_type ids.