“徽章"的数据库架构系统&任意标准 (MySQL/PHP) [英] Database Architecture for "Badge" System & Arbitrary Criteria (MySQL/PHP)
问题描述
总而言之，我对如何设计这样一个数据库感到有些困惑，该数据库允许无限期创建徽章规则，而不需要对数据库中先前存在的用户表进行结构更改.
To summarize, I'm a little confused as to how I would design such a database that allows indefinite badge-rule creation without requiring structural changes to the previously existing user-tables in the database.
存储徽章标题、标准等.该表会是什么样子?
Storing Badge Title, Criteria, etc. What would that Table look like?
- badge_id (1)
- badge_title(10K 徽章)
- badge_image (10k.jpg)
- badge_criteria ([posts] >= 10000)
...
我想在我自己的个人项目中实施一个徽章系统，但我正在寻求一些关于如何最好地做这样的事情的建议.我一直在阅读这里关于徽章系统的一些问题，但没有看到数据库架构受到太多关注.
I would like to implement a badge-system on my own personal projects, but am seeking a bit of advice as to how one would best do such a thing. I've been reading some of the questions here about badge-systems, but don't see the database-architecture getting much attention.
基于用户积分的徽章(假设的10k 徽章")看起来非常简单.任何影响用户声誉的事件(赞成、反对、回答接受等)都会调用一种方法来审查用户的新声誉，并可能授予徽章.
Badges that are based on user-points (Hypothetical "10k Badge") would seem pretty straight forward. Any event that affects the users reputation (upvotes, downvotes, answer-accepted, etc) would invoke a method to review the users new reputation, and potentially award a badge.
这个系统听起来很简单，但是对于想要在未来不费吹灰之力创建无数徽章的管理员来说，它是什么样的数据库 - 其中一些可能基于不同的标准，而不是只是用户声誉.
That system sounds pretty straight forward, but what does that look like as a database for the administrator who wants to create innumerable amounts of badges with little-effort down the road - some of which may be based on different criteria, and not simply the user reputation.
用户声誉可能是用户记录本身中的一个值.但理想情况下，您是否不想在创建新徽章时避免向用户表添加新字段?例如编辑的 100 个条目"徽章 - 您不会创建新列entries_edited"；在用户表中，你会吗?然后在每个条目编辑后增加它...
User-reputation is likely a value within the user-record itself. But ideally, wouldn't you want to avoid having to add new fields to the user table when you create new badges? For instance the "Edited 100 Entries" badge - you wouldn't create a new column "entries_edited" within the Users Table, would you? And then increment that after each entry-edited...
有什么提示吗?
Stackoverflow 存档:
Stackoverflow Archive:
注意:我不是在问如何将徽章与用户相关联.我不是在问如何授予徽章(这将以编程方式完成)
推荐答案
鉴于徽章标准可以任意复杂，我认为您无法将其存储在分解为简单"数据元素的数据库表中.尝试编写一个可以处理任意复杂标准的规则引擎"将使您走上重新编写编程语言中所有工具的道路.
Given that the badge criteria can be arbitrarily complex, I don't think you can store it in a database table broken down into "simple" data elements. Trying to write a "rules engine" that can handle arbitrarily complex criteria is going to take you down the path of basically re-writing all the tools that you have in your programming language.
如果您事先知道您希望徽章仅限于某些字段(即徽章仅基于声誉或编辑次数或其他内容)，那么您可以将它们存储在一个简单的表格中，例如:
If you know in advance you want the badges limited to only certain fields (i.e. badges are only based off reputation or number of edits or something), then you can store those in a simple table like:
ReputationBadgeCriteria
BadgeId
BadgeName
MinReputation
或者，您可以使用某种 DSL 来编写您的规则"，但您最终还必须创建一个解析器来在您阅读规则时解析规则以及执行这些规则的内容.根据您想要的 DSL 的复杂性，这可能不是一项微不足道的任务.这看起来像您在问题中使用的路径，其中包含一个 Criteria 列(大概是纯文本)，其中包含[Reputation] > 1000"或[Posts] > 5"之类的内容.您仍然需要解析和执行这些规则，而为此编写某些内容的复杂程度取决于您希望这些规则的复杂程度.
Alternatively, you could use some kind of DSL to write your "rules" but you end up having to also create a parser to parse the rules when you read them as well as something to execute these rules. Depending on the complexity you want in your DSL, this may not be a trivial task. This looks like the path you are going in your question with having a Criteria column (presumably plain text) that has something like "[Reputation] > 1000" or "[Posts] > 5" in it. You still have to parse and execute those rules and the complexity of writing something to do so is dependent on how complex you want those rules to be.
我建议您阅读these Daily WTF 文章 了解为什么这种方法会导致痛苦.
I would recommend you read these Daily WTF articles for information on why this approach leads to pain.
这篇关于“徽章"的数据库架构系统&任意标准 (MySQL/PHP)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
