Database Design - Point Storage Approach for Users

Question

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

On my site, a user can get points for performing various actions. There are currently 3 events for which I reward points, but the design must be scalable, where I can add other actions to reward points.

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

So this is what I have so far, but it does not look right :)

Points_Table point_id User ID action (This will record for which action points are given) Points

I should be able to deduce from the database that this user received xxxx points to create this store or to contact these friends or to answer this question. The above design will obviously not take care of this.

Thank you for your advice.

Answer 1

Do not store dots at all. Just make inquiries on this view. Thus, it will be sustainable in the face of arbitrary change.

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:

. : .

, . . , , . , , , , . , - . , .

, .

, , , , .

user_id action_type . , , !

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

Answer 2

, ( , ), . - Reward_Actions (ActionId, ActionType, ) .., .

Answer 3

User, Action User_Action , . , , , , . , , , , .

Answer 4

If you want you to be able to track the action in a row in the repository or response table, add the fourth column, which is the pk entry for the row added to the action.