数据库设计 - 为用户存储点的方法 [英] Database design - Approach for storing points for users

问题描述

只需查找有关如何处理数据库设计的一些建议。

在我的网站上，用户可以获得执行不同活动的积分。目前有3个活动，我奖励积分 - 但设计必须是可扩展的，我可以添加其他活动奖励点。

今天 - 用户获得积分
1）当他添加一个新商店时，他获得10分（商店信息存储在STORE表中）
2）当他回答一个问题时，他得到7分（问题/答案存储在ANSWERS表）
3）当他指向加入网站的朋友时，他得到5分

所以这是我到目前为止 - ：）

Points_Table
point_id
user_id
操作（这将捕获指定点的操作）
点

我应该能够从数据库中推断出这个用户有创建这个商店的xxxx积分，或者引用这些朋友或回答这个问题。

上面的设计显然不能解决这个问题。 >解决方案

不要存储点。只需对此视图进行查询。

  create view UserPoints 
 as 
 select 
 created_by_id作为user_id，
'STORE'作为action_type，
 store_id作为action_id，
（从action_points中选择点，action_desc ='create store'）作为点
 store 
 union 
 select 
 user_id，
'ANSWER'as action_type，
 question_id as action_id，
（从action_points中选择点，其中action_desc ='answer问题'）as points 
 from answer 
 union 
 select 
 refer_by_id as user_id，
'REFERRAL'as action_type，
 refer_id as action_id，
 （从action_points中选择点，其中action_desc ='referral'）作为点
从引荐
  

添加：

这遵循正常的数据库设计原则。所有数据库归一化可以用下面的总结：不重复数据。

包含点表基本上只是重复数据库的其余部分已经包含的信息。因此，应该省略。当考虑到必须去的长度以保持数据的一致性时，这是清楚的。不用担心这些问题，如果引荐归因于Alice，但后来确定Bob应该接受信用，则这将需要改变单个表的单个行中的单个字段。如果包括点表，那么它也必须以某种方式更新。如果点摘要被存储，那么上帝可以怜悯你的数据库。

不是存储点，而是可以使用视图或存储过程，然后点数系统会在数据变化时自动调整。

我认为在这种情况下，一个视图对于存储过程是有影响的，因为点数据可能有很多种方法需要进行分析，这提供了最大的灵活性，并且它为优化器提供了识别最佳路径的最佳机会。

您可以通过user_id从视图中选择，通过action_type或求和点列以获取用户或所有用户的总计。看看你可以有的乐趣，它几乎是免费的！

 选择
 sum（up.points）as total_points ，
 up.user_id，
 u.user_name 
从
 UserPoints up 
加入用户u on up.user_id = u.user_id 
 group by 
 user_id，
 user_name 
 order by 
 total_points desc 
  

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

解决方案

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 consistency 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 flexibility, 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

这篇关于数据库设计 - 为用户存储点的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！