如何在数据库ERD中建模类似于钻石的多对多关系

Question

图例：

• PK（蓝色）：主键
• FK（绿色）：外键
• PFK（蓝色）：同时为主键和外键

如何建模钻石型关系？以下用简化的例子进行解释。

有三个实体： organization，item 和 tag。

我的目标是建模：

1. 每个 tag 本身都是唯一的，并且属于一个单独的组织。
2. 每个 item 本身都是唯一的，并且属于一个单独的组织。
3. items 拥有多个标签（通过 M2M 表连接），并且相关的 tag/item 必须属于同一个组织。（即来自组织 A 的 item 不能与来自组织 B 的 tag 匹配）

我画了两个备选方案，但它们都无法满足我的需求。

图1 违反第三个目标：使用 id 作为主键，items 和 tags 本身是唯一的，但没有阻止将属于不同组织的组合插入到 item_tag 中。

图2 不违反要求，但扭曲了第一和第二个目标：在 item 和 tag 表中添加了 organization_id 作为主键和外键，并且 item_tag.organization_id 列引用了这两个表。这可以防止来自不同组织的组合。现在，tag.id 和 item.id 列成为了一个不必要的复合主键，因为实际上单个 id 列代表了 item 和 tag 的唯一性。

如何正确建模这些要求呢？

- ozm

---

“FOREIGN KEYs” 有其限制。你可能已经达到了它们的限制，如果是这样，你需要编写应用程序代码来强制执行约束条件。 - Rick James

Answer 1

为了强制实施引用完整性，您需要：

• 在所有表中包括organization_id
• 在tag和item两个表中创建逻辑冗余的UNIQUE（或PK）约束，其列为(organization_id，id)
• 在item_tag中具有多列FK约束，匹配那些UNIQUE约束的列。

如果不包含organization_id（逻辑上冗余），则没有任何东西可以防止您错误地链接来自不同组织的项目和标签。

这将是您的diagram 2。但是，您真的需要uuid数据类型来存储标签吗？bigint甚至int应该足够，而且更小更快。

与PostgreSQL相关的紧密联系的案例及代码示例：

• 强制执行“两个表之外”的约束

- Erwin Brandstetter

---

Brandsletter，感谢您的回答。这里的uuid仅用于举例说明。所提出的解决方案与图2（PFK：主键+外键）完全相同，但我希望避免向UNIQUE或PK填充不必要的列。 - ozm

@ozm：确实如此。我之前尝试过相同的方法，但没有找到更好的方式，而不需要像链接答案中演示的那样冗余地使用“UNIQUE”约束。 - Erwin Brandstetter