在数据库中存储用户积分系统的最佳方式
我目前正在开发一个小型应用程序，用户可以通过完成任务来累积积分。我想要的是关于设计数据库表的最佳方法的一些建议。在某些方面，该系统与 SO 类似，用户完成任务（在 SO 上回答问题），然后更新他们的积分。
我目前有一个用户表，用于存储常用的用户信息，但不知道如何继续下去。
我最好有一个积分表（用户有很多积分），然后添加 user_id 和积分数量；那么在查询用户点时，提取具有给定 user_id 的所有点并对它们求和？
有没有更有效的方法来做到这一点？
谢谢，
杰克
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 user_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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果您只关心总分，那么按照 Oded 的建议，只需将该字段直接包含在
User表中即可。但是，如果您需要知道这些积分是如何获得的，也许您可以有一个积分交易表。然后，该交易表可以具有指向User表的user_id字段和各种字段（如Action、Points）的外键，日期。为什么用户会有很多积分？例如，与各种类别相关的点？正如 Oded 也提到的，这取决于这些要点的含义以及您想了解它们的哪种信息（也与获得它们或对它们产生影响的用户有关，即对答案进行投票会减少发布用户积分）。
If you're only concerned with point total, then as Oded suggested, just include the field directly in the
Usertable. 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 theUsertable'suser_idfield and various fields likeAction,Points,Date.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).
额外积分表是一种公平的方法，因为它可以跟踪获得积分的原因。根据用户数量、积分交易和使用的服务器，我会考虑持有一些常用的聚合：最常见的是用户总积分。
要在积分表中插入新的积分记录，您可以使用存储过程，该存储过程不仅会在积分表中记录积分，还会增加每个用户的积分聚合。该聚合可以保存在用户表中的单独列中（按照 Oded 的建议）。
随着设计的进步，可能会出现可以预先计算的新的有用聚合。
但为了简单起见：仅在绝对必要时才使用这些聚合，例如计算用户的总积分需要很长时间。对于小型系统来说，动态计算这些总数确实不应该成为问题。
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.
如果
points在没有用户的情况下没有任何意义，并且与用户是一对一的关系，我会将该字段直接放在user表上。但是，如果您记录每个用户不同类型的积分，我将创建一个
points_type查找和一个以用户为键的points表，并且points_typeID。If
pointsdon't have any meaning without a user and are a one-to-one relationship with it, I would put the field directly on theusertable.If, however, you are recording different types of points per user, I would create a
points_typelookup and apointstable keyed to theuserandpoints_typeids.