Database design - Approach for storing points for users

Question

Just looking for some suggestions on how to approach the database design for this.

On my site a user can get points for performing different activities. Currently there are 3 activities for which I award points - but the design has to be scalable where I can add other activities for awarding points as well.

So today - the user gets points 1) When he adds a new store he gets 10 points (Store information is stored in STORE table) 2) When he answers a question he gets 7 points (Questions/answers are stored in a ANSWERS table) 3) When he refers friends who join the site he gets 5 points

So this is what I have so far - but it doesnt look right :)

Points_Table point_id user_id action (This will capture for what action the points are being given) points

I should be able to deduce from the database that this user got xxxx points for creating this store or for referring these friends or for answering this question. The above design obviously doesn't take care of that.

Thanks for your tips

Answer 1

If you do want to be able to trace the action back to a row in the store or answers table, add a fourth column, which is the pk entry for the row that action added.

Answer 2

Add a table that stores the actions (generic table listing actions that point to the actual action in different table) for which the user gets points. The table might be something like Reward_Actions(ActionId, ActionType, Points earned) etc. and you can add different types of rewarding actions through this.

Answer 3

A typical design for this would be to have the User table, the Action table, and the User_Action table for recording all actions the user fulfills. You'd need the two foreign keys and probably a primary key in there, along with a date stamp for when they accomplished it. Then you could store the points for each action in the Action table, and whenever you do a lookup for total points, just join the the tables and order by the timestamp so you can get a history of their points.

Answer 4

Don't store the points at all. Just do queries on this view. That way it will be robust in the face of arbitrary changes.

create view UserPoints
as
select
    created_by_id as user_id,
    'STORE' as action_type,
    store_id as action_id,
    (select points from action_points where action_desc='create store') as points
from store
union
select
    user_id,
    'ANSWER' as action_type,
    question_id as action_id,
    (select points from action_points where action_desc='answer question') as points
from answer
union
select
    referred_by_id as user_id,
    'REFERRAL' as action_type,
    referred_id as action_id, 
    (select points from action_points where action_desc='referral') as points
from referral

Edited to add:

This follows the normal database design principles. All database normalization can be summed up with this: don't repeat data.

The inclusion of a points table essentially just repeats information that the rest of the database already contains. Therefore, it should be omitted. This is made clear when considering the lengths to which one must go to maintain the consistancy of the data. Without worrying about the points, if a referral is attributed to Alice, but later it is determined that Bob should receive the credit, this will require a change to a single field in a single row of a single table. If a points table is included, then it must also be updated in some way. If points summaries are being stored, then may God have mercy on your database.

Rather than storing the points, a view or stored procedure may be used instead, and then the points system is adjusted automatically when the data changes.

I think a view is perferrable to a stored procedure in this case, because there are many ways in which the points data may need to be analyzed, and this provides the most flexability, and it gives the optimizer the best chance at identifying the optimal path.

You may select from the view by user_id or by action_type or sum the points column to get totals for a user or for all users. Look at the fun you can have, and it's practically free!

select
    sum(up.points) as total_points,
    up.user_id,
    u.user_name 
from 
    UserPoints up 
    join user u on up.user_id = u.user_id 
group by 
    user_id, 
    user_name 
order by 
    total_points desc