多个多对多关系建模的最自然方法

Question

假设我有“书籍”和“人物”。一个人可以创作很多书，一本书可以由很多人创作。一个人读过很多书，一本书被很多人阅读过。

Person               Book
------               -----
personId             bookId

我可以使用两个关系表：

has_read             has_authored
--------             ------------
personId, bookId     personId, bookId

选项一：

person_book_relation
--------------------
personId, bookId, relationType ("read", "authored")

另一个例子可能是Actor和Event之间的某种订阅者/发布者关系。

有没有选择哪个的指导方针？

如果有更多类型的关系，这会改变你的解决方案吗？

一个团队有很多人扮演不同的角色。一个人可以在很多个团队中。 (这只是举个例子)

Team_Person_relation
--------------------
TeamId, PersonId, Role ('Defender', 'Attacker', 'Goalkeeper', 'Midfielder'... etc)

如果您使用单独的表格，那么至少需要4个表格。但是，团队角色似乎比“阅读/撰写”关系更相互关联？

- KTrum

---

另一种可能性是使用4个表：Person（人），Book（书），Action（动作），PersonBookAction（人-书-动作）。 - Kermit

好的观点，"action" 绝对需要以某种方式确保其完整性。 - KTrum

Answer 1

我会使用第二种表格，除非我遇到一种情况，即关系类型实际上影响表中的列。

例如，在书籍示例中，作者可能有一个将其发送给出版商的日期，这无效了将所有信息保存在一个表中的想法，因为该信息不适用于读者。

同样，“进球挽救”只适用于你的守门员。

我想诚实地说，如果有点陈腐的回答是“取决于您要提取的信息”，但通常情况下，您越明确地显示“这是描述表x和表y之间关系的表”，您的数据库就越清晰易于维护。

- Philip Adler

---

如果关系是通用的（没有额外列的额外数据），最好只使用一个表？特别是如果您经常同时想要所有关系。就像在团队示例中，我猜您经常想要检索整个团队。当只有两个关系时，例如作者/读者。为了清晰起见，将它们分开可能很好。 - KTrum

Answer 2

这主要取决于您作为数据库创建者，因为两种解决方案都是正确的。需要考虑的主要是数据将来如何使用（或者至少现在预测将如何使用）。以下是一些例子：

1. 如果在一个表中强制使用太多的多对多关系，那么在处理其中一个关系时，你需要始终记住“还有其他关系”。例如，如果你想查看没有撰写任何书籍的所有人，你需要构建左连接查询，以过滤“阅读”关系。随着查询变得越来越复杂，并包括更多的表和更多的外部连接，很容易得到不必要的结果。

2. 团队角色的示例表明，角色列表可能会在未来发生变化。因此，将此角色保留在关系列中是更好的解决方案。此外，该关系在这里指定“成为团队成员”，而此成员身份的角色仅是该成员身份的属性。

3. 如果你希望在你的多对多关系中存储一些附加信息（例如创作日期或读者喜欢的程度），它将建议使用单独的表，否则将使用许多稀疏列来处理所有可能的关系。

4. 最后但并非最不重要的是性能。如果表试图包含太多“无关”的数据，则有时可能很难有效地设计和使用索引。

- Kuba Wyrostek

---

在团队示例中，我猜你经常想同时检索所有角色，如果它们都包含在同一张表中会更容易。 - KTrum

越想越觉得这是一个问题，我们是要建模“处于关系中”（单独的表）还是“作为某个关系的属性”（公共表）。在人-书例子中，公共表意味着“书和人之间存在某种关系”，这是无意义的。 - Kuba Wyrostek

你能给出一个例子吗？这样我就可以理解你所指的“关系”和“某些关系的属性”的区别了。 - KTrum