mysql - 如何在数据库 ERD 中模拟多对多关系的菱形模型

Question

图例:

• PK(蓝色):主键
• FK(绿色):外键
• PFK(蓝色)同时是主键和外键

如何模拟菱形关系(如果术语正确)？最好用一个简化的例子来解释:

有organization、item和tag实体。

我的目标是建模:

1. 每个 标签 都是独一无二的，并且属于一个组织。
2. 每个 item 都是独一无二的，并且属于一个组织。
3. 项目有很多标签(使用 M2M 表连接)和相关的 tag/item 对必须属于同一组织。 (即来自组织 A 的项目不能与来自组织 B 的标签配对)

我绘制了两个备选解决方案，但没有一个让我满意。

图 1 打破了第三个目标:items 和 tags 是唯一的，它们使用 id 作为主键，但是没有什么可以阻止将对插入属于不同组织的 item_tag。

图 2 没有中断，但改变了第一个和第二个目标:organization_id 作为主键和外键添加到 item 和 tag 表和 item_tag.organization_id 列引用两者。这可以防止来自不同组织的配对。 tag.id 和 item.id 列现在是不必要的复合主键的一部分，因为实际上单个 id 列代表 项目 和标签。

我如何正确地为这些需求建模？

Answer 1

要实现参照完整性，您必须...

• 包括organization_id在所有表格中
• 创建逻辑冗余UNIQUE (或 PK)对 (organization_id, id) 的约束在两个表中 tag和 item
• 在 item_tag 中有多列 FK 约束匹配那些 UNIQUE 的列限制条件。

如果您不包括 organization_id (逻辑上是多余的)没有什么可以阻止您(错误地)链接来自不同组织的项目和标签。

那将是您的图表 2。但是你真的需要数据类型uuid吗？标签？ bigint甚至 int应该足够了，同时更小更快。

与 PostgreSQL 代码示例密切相关的案例:

• Enforcing constraints “two tables away”