使用连接表与类型字段的权衡
我正在深入研究设计一个带有与多个其他表相关的表的数据库。一个具体的例子是一个对不同类型的事物进行评论的网站 - 人们可以对文章发表评论，或者他们可以对照片发表评论，或者他们可以对人发表评论。
似乎有两种方法可以表示这一点：
1）相互连接表 表：
- 文章 文章
- _评论
- 评论
- 评论_人物
- 评论_照片
- 人物
- 照片
或 2)
- 文章
- 评论
- 人物
- 照片
，评论表将有一个“type”字段和一个 item_id 来链接回另一个表。
第一种方法似乎更“合适”，使用外键限制似乎应该没有问题，而第二种方法具有更少的表，并且在某些方面可能“更简单”，但我们可以使用 FK 限制，因为 item_id 可以与多个相关FK（据我所知 - 使用 mysql innodb）。最好不要在我们的应用程序中存在 2-3 个具有多个关系（评论、照片等）的表和 5-10 个需要关系的表。
我正在寻求有关哪种方法更好的建议。
I'm looking insight into designing a database with a table which is related to multiple other tables. A specific example is a site with comments on different kinds of the things - people could comment on articles, or they could comment on photos or they could comment on people.
It seems that there are two ways to represent this:
1) join tables for each other table
TABLES:
- articles
- articles_comments
- comments
- comments_people
- comments_photos
- people
- photos
or
2)
- articles
- comments
- people
- photos
and the comment table will have a "type" field and a item_id to link back into the other table.
The first approach seems more "proper" and it seems we should have no problem using foreign key restraints whereas the second approach has fewer tables and might be "simpler" in some respect, but we can use FK restraints since the item_id can related to multiple FKs (AFAIK - using mysql innodb). It might be good to not that in our application there might be 2-3 tables that have multiple relations (comments, photos, etc) and 5-10 tables that need relations.
I'm looking for advice on which is the better approach.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我个人不喜欢选项#2。它代表了一个有争议的话题，即“一大查找表”概念。
其一，我同意伊兹米尔的观点——管理起来很麻烦。而且，它代表了面向对象对关系数据库设计的强加，这有点像混合苹果和橙子。虽然对于具有 OO 编程背景的人（包括 ME...）来说很容易想象一个“COmment 对象”及其属性，但请思考一下 DB 设计。示例 1 中的表名清楚地描述了它们所代表的实体中实际发生的情况以及它们之间的关系（多对多引用表）。它们还代表了更好地遵守“正常”形式。
RBDMS 是关于表示实体及其之间的关系，并不一定总是符合 OO 的原则。此外，标准化规则更有利于选项#1。
对我来说，选项#1 代表了更强大的 RDB 设计，并且更易于管理、更易于扩展，而且可能执行得更快。
I personally don't like option #2. It represent what is a controversial topic, the ONE BIG LOOKUP TABLE Concept.
For one, I agree with Yzmir - icky to manage. Also, it represents the impostion of OO on the design of a relational database, which is a little like mixing apples and oranges. While it is easy for someone with an OO programming background (that includes ME . . . ) to imagine a "COmment Object" and it's properties, think for a moment about DB Design. The Tablenames in example 1 clearly desrcribe what is actually going on in both the entities they represent, and the relationships between them (the many-to-many reference tables). They also represent far better adherence to "Normal" form.
RBDMS is about representing entities and the relationships between them, and is not necessarily always consistent with the principles of OO. Further, the rules of normalization are more in favor of option #1.
Option #1, to me represents stronger RDB design, and is both easier to manage, more easily extensible, and will probably perform faster as well.
带有
type列（顺便说一句，枚举）的comment表似乎是更好的方法，因为您可能希望将来发展“评论”的含义。拥有您所描述的四个表也有助于您的模型类字段及其关系更容易。
如果注释表是 0 到多关系，请使用“LEFT JOIN”。否则它是“内部连接”。
享受。
The
commenttable with thetypecolumn (an enum btw), seems like the better approach because you may want to evolve what a ''comment'' is in the future.Having the four tables like you described it also lends itself to your Model class fields and their relationships easier.
Use a 'LEFT JOIN' to the comments table if its a 0-to-many relationship. Otherwise its an 'INNER JOIN'.
Enjoy.