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
所以今天 - 用户获得积分1)当他添加新商店时,他获得10分(商店信息存储在STORE表中)2)当他回答问题时,他得到7分(问题/答案存储在ANSWERS表中) 3)当他引用加入该网站的朋友时,他得到5分
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
Points_Table point_id user_id action(这将捕获给出的点的动作)点
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.
我应该能够从数据库中推断出该用户获得xxxx积分用于创建此商店或引用这些朋友或回答此问题。上述设计显然没有解决这个问题。
Thanks for your tips
谢谢你的提示
4 个解决方案
#1
2
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.
包含点表基本上只是重复数据库其余部分已包含的信息。因此,应该省略。在考虑必须保持数据一致性的长度时,这一点很清楚。在不担心这些要点的情况下,如果推荐归因于Alice,但后来确定Bob应该收到该积分,则需要更改单个表的单行中的单个字段。如果包含点表,则还必须以某种方式更新点表。如果存储点汇总,那么上帝可以怜悯您的数据库。
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!
您可以通过user_id或action_type从视图中进行选择,或者将points列相加以获得用户或所有用户的总计。看看你可以拥有的乐趣,它几乎是免费的!
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
#2
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.
添加一个表,用于存储用户获取点数的操作(通用表列出指向不同表中实际操作的操作)。该表可能类似于Reward_Actions(ActionId,ActionType,Points points)等等,您可以通过此添加不同类型的奖励操作。
#3
1
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.
典型的设计是使用User表,Action表和User_Action表来记录用户完成的所有操作。你需要两个外键,可能还有一个主键,以及完成它时的日期戳。然后,您可以将每个操作的点存储在Action表中,每当您查找总点数时,只需加入表格并按时间戳排序,这样您就可以获得其点数的历史记录。
#4
0
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.
如果您确实希望能够将操作追溯到商店或答案表中的某一行,请添加第四列,该列是该操作添加的行的pk条目。
#1
2
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.
包含点表基本上只是重复数据库其余部分已包含的信息。因此,应该省略。在考虑必须保持数据一致性的长度时,这一点很清楚。在不担心这些要点的情况下,如果推荐归因于Alice,但后来确定Bob应该收到该积分,则需要更改单个表的单行中的单个字段。如果包含点表,则还必须以某种方式更新点表。如果存储点汇总,那么上帝可以怜悯您的数据库。
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!
您可以通过user_id或action_type从视图中进行选择,或者将points列相加以获得用户或所有用户的总计。看看你可以拥有的乐趣,它几乎是免费的!
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
#2
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.
添加一个表,用于存储用户获取点数的操作(通用表列出指向不同表中实际操作的操作)。该表可能类似于Reward_Actions(ActionId,ActionType,Points points)等等,您可以通过此添加不同类型的奖励操作。
#3
1
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.
典型的设计是使用User表,Action表和User_Action表来记录用户完成的所有操作。你需要两个外键,可能还有一个主键,以及完成它时的日期戳。然后,您可以将每个操作的点存储在Action表中,每当您查找总点数时,只需加入表格并按时间戳排序,这样您就可以获得其点数的历史记录。
#4
0
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.
如果您确实希望能够将操作追溯到商店或答案表中的某一行,请添加第四列,该列是该操作添加的行的pk条目。