在关系型数据库中建模同一实体之间的多对多关系

Question

当两个表之间存在多对多的关系，或者一个表与自身存在多对多的关系时，我们可以使用连接表来建模。

然而，关系的类型可能会超出最初的类型，例如：

需求：我们有用户。每个用户可以有0个或多个其他用户作为朋友。

解决方案：一个用户表和一个名为“user_user”的连接表。

然后我们发现一个新的需求：一些友谊是浪漫的，换句话说，两个用户可以以不同于友谊的方式相连。

解决方案a：向连接表添加一个包含友谊类型（朋友|伴侣|前任）等的列。

解决方案b：将'user_user'表重命名为'friendships'（这将是此方法更好的名称），并创建一个新表称为romances，也将用户连接到用户。

然后我们又发现了另一个新需求：用户可以欠其他用户钱。再次，我们可以在同一个连接表上附带或创建一个名为'debts'的新表。这次我的直觉是100％要有一个单独的表格。

每当我们发现新类型的关系或已知关系的子类型时，我们都可以向一个连接表中添加列或创建一个新的连接表。

我的问题是，什么是决定何时必须创建新表的良好规则？

当我们必须按顺序对每个有序对进行多行操作时，是否需要创建新表？例如，如果过去的关系从未被删除，因此如果两个用户在过去的两年中曾经是朋友，失去联系，然后重新成为朋友，我们也希望保留旧行，并具有开始和结束日期，但是有两行会使其他列信息重复（旧行显示一个用户欠钱，新行不显示）。

当额外的列在逻辑上不是单个关系类型的定义时，是否需要创建新表？

好的：友谊开始日期直接与友谊1对1相关。

不好：一位用户拥有另一位用户多少钱的列可以对于该关系是1对1的，但在逻辑上不是友谊的描述。

如果我们事先知道两个表将有很多多对多关系，那么是计划许多连接表的原因还是制作一个更灵活的单个连接表（即使连接的类型不是任意的）？

- user1852503

Answer 1

"由你决定。你可以将“友谊”定义为与“恋爱关系”不同的关系，并因此需要两个连接表。或者你可以将“恋爱关系”定义为“友谊”的特殊情况，因此只需要一个表格。我倾向于后者，因为它更简单， less容易混淆。(如果同样的两个人既被列为朋友，又被列为恋人，那怎么办？)

然而，“欠钱给”是不同的。如果A和B是朋友，那么B也是A的朋友。但是如果A欠B钱，说B欠A钱是错误的。因此，您不仅要定义不同类型的关系，而且位置字段包含对A的引用和哪个包含对B的引用变得重要。因此，最好有一个单独的连接表。"

- TommCatt

Answer 2

简而言之，表格代表着应用程序/业务关系或者关联。就像实体-关系模型中的关系一样。也就是说，关系模型中的关系。每当我们对一个不同的关系感兴趣时，我们就会考虑一个新的（“连接”/“关联”）表格。规范化告诉我们何时以及如何将一个关系/表格分解成其他关系/表格或将某些关系/表格合并为一个。

---

我们通过某个谓词（即语句模板）来确定关系，该谓词将行转化为一个命题（即语句），该命题表达了我们对应用程序感兴趣的内容。符合表的谓词的真实命题所对应的行将被放入表中。

Employee(e, n, ...) -- employee identified by E is named N and ...
Manages(e, m) -- employee M manages employee E

如果不知道谓词，你就无法了解表格对应的应用程序或根据应用程序进行设置！当你陈述“关系”的“基数”时，你必须首先确定你所说的是哪种关系/联合/谓词。

（不幸的是，“关系”被许多所谓的ER方法和产品用来表示表格的外键。）

当我们可以从新的表格中重新构建原始表格时，我们可以将多个表格合并到一个表格中或替换一个表格。在这里，我们可以将上面的表格合并成一个表格。

-- employee identified by E is named N and ... AND employee M manages employee E
EmployeeM(e, n, ..., m)

这通常是Employee和Manages的JOIN，因为JOIN的谓词是其参数谓词的AND。但由于E是公共列集并且其中一个是唯一的，我们可以通过新的投影重建原始数据。

如果员工只能有一个经理，那么这将是合理的设计。但是，如果员工可以有一个或多个经理，那么该表虽然仍将包含满足谓词并允许重构原始数据的行，但会出现更新异常，因此我们更喜欢原始数据。如果员工可以有零个或一个或零个或多个经理，那么我们仍然可以使用该表，但不仅限于该表，因为它无法告诉我们没有经理的员工（而原始设计可以）。我们还需要一个类似于Employee但具有不同谓词员工E的标识符为N且...且E没有经理。的表。或者，我们可以仅使用像EmployeeM一样的表，它通过不同的谓词允许nulls员工E的标识符为N且...且（员工M管理员工E或M为空且E没有经理）。

阅读有关原始的Chen ER模型/方法，其中包括实体类型、图标和表格以及关系类型、图标和表格。每个关系类型都清晰地绘制为一个菱形。每条线都清晰地表示实体类型（可能是联接实体类型）参与关系类型，即清晰地表示表格之间的外键关系。而许多所谓的ER方法甚至不区分实体和关系。（这不一定好或坏，但他们的介绍往往不能解释如何正确建模。）

关系模型并不在乎实体和关系本身。它的表格只是基于值的关系。ER和所谓的ER方法在实体和关系之间做了不必要和限制性的武断区分。当然，一个表格仍然可以像ER模型那样描述应用程序，因为值的子行可以标识实体。请参见this answer。

规范化告诉您何时以及如何将关系的谓词/表格分解为更小的谓词/表格（它们是其投影，并且通过AND/JOIN返回），以及何时和如何将多个关系的谓词/表格合并为一个（通过AND/JOIN，使用投影进行重构）。

附言：重复值本身并没有错。可能会有问题的是，当多个行根据您选择的谓词在表中存在或不存在时，它们都暗示应用程序相同的内容。并非所有这样的冗余性都是错误的；只有无法控制的冗余性才是错误的。通常，我们希望设计尽可能符合第五范式（即减少更新异常、谓词复杂性和约束）。

- philipxy