成就/徽章系统 [英] Achievements / Badges system

问题描述

我一直在浏览这个网站以获得答案，但我仍然有点不确定如何在其数据库结构和实现方面规划类似的系统.

I have been browsing this site for the answer but I'm still a little unsure how to plan a similar system in its database structure and implementation.

在 PHP 和 MySQL 中，很明显一些成就是立即获得的(当采取专门的行动时，在 SO 情况下:填写所有个人资料字段)，尽管我知道 SO 会在一段时间后更新并分配徽章.有这么多用户&徽章不会造成性能问题(就规模而言:用户和徽章的数量都很高).

In PHP and MySQL it would be clear that some achievements are earned immediately (when a specialized action is taken, in SO case: Filled out all profile fields), although I know SO updates and assigns badges after a certain amount of time. With so many users & badges wouldn't this create performance problems (in terms of scale: high number of both users & badges).

所以我假设的数据库结构很简单:

So the database structure I assume would something as simple as:

Badges     |    Badges_User      |    User
----------------------------------------------
bd_id      |    bd_id            |  user_id
bd_name    |    user_id          |  etc
bd_desc    |    assigned(bool)   |  
           |    assigned_at      |

但正如一些人所说，采用渐进式风格方法会更好，这样拥有 1,000,000 个论坛帖子的用户就不会减慢任何功能.

But as some people have said it would be better to have an incremental style approach so a user who has 1,000,000 forum posts wont slow any function down.

那么它会是另一个徽章表，可以是增量的还是只是上面badges_user 表中的进度"字段?

Would it then be another table for badges that could be incremental or just a 'progress' field in the badges_user table above?

感谢阅读，请关注所需系统的可扩展性(例如成千上万的用户和 20 到 40 个徽章).

Thanks for reading and please focus on the scalability of the desired system (like SO thousands of users and 20 to 40 badges).

为了消除一些混淆，我将_at 指定为日期/时间，授予徽章的标准最好放在每个徽章的准备好的查询/功能中，不是吗?(更好的灵活性)

to some iron out some confusion I had assigned_at as a Date/Time, the criteria for awarding the badge would be best placed inside prepared queries/functions for each badge wouldn't it? (better flexibility)

推荐答案

我认为您建议的结构(没有根据评论的已分配"字段)可以工作，添加一个额外的表，说"Submissions_User"，包含对 user_id & 的引用用于计数提交的递增字段.那么您只需要一个事件侦听器"，按照 这篇文章我想你会准备好.

I think the structure you've suggested (without the "assigned" field as per the comments) would work, with the addition of an additional table, say "Submissions_User", containing a reference to user_id & an incrementing field for counting submissions. Then all you'd need is an "event listener" as per this post and methinks you'd be set.

对于成就徽章，在每次提交时运行事件侦听器(仅针对提交课程的用户)，并当场授予任何相关徽章.对于基于时间的徽章，我每晚都会运行 CRON 作业.循环浏览完整的用户列表一次，并根据情况授予徽章.

For the achievement badges, run the event listener upon each submission (only for the user making the submission of course), and award any relevant badge on the spot. For the time-based badges, I would run a CRON job each night. Loop through the complete user list once and award badges as applicable.

这篇关于成就/徽章系统的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！