MySQL：两个 n:1 关系，但不能同时存在

Question

对不起，这个标题很难解释。

我需要一个与此类似的数据模型：

如您所见，集合可以同时属于用户或学校。我的问题：它应该只允许属于用户或学校。但永远不会同时两者。

我该如何解决这个问题？

Answer 1

您当前的设计称为“独占弧”，其中“sets”表有两个外键，并且需要其中一个为非空。这是实现多态关联的一种方法，因为给定的外键只能引用一个目标表。

另一个解决方案是创建一个用户和学校都引用的通用“超级表”，然后将其用作集的父级。

create table set_owner

create table users 
  PK is also FK --> set_owner

create table schools
  PK is also FK --> set_owner

create table sets 
  FK --> set_owner

您可以将其视为类似于 OO 建模中的接口：

interface SetOwner { ... }

class User implements SetOwner { ... }

class School implements SetOwner { ... }

class Set {
  SetOwner owner;
}

---

回复您的评论：

所以 SetOwner 表包含 UserID 和 SchoolID，对吗？这意味着我将不允许用户和学校使用相同的 ID。我如何强制执行此操作？

让 SetOwners 表生成 id 值。您必须先插入 SetOwners，然后才能插入 Users 或 Schools。因此，请使“用户”和“学校”中的 id不自动递增；只需使用 SetOwners 生成的值：

INSERT INTO SetOwners DEFAULT VALUES; -- generates an id
INSERT INTO Schools (id, name, location) VALUES (LAST_INSERT_ID(), 'name', 'location');

这样，学校和用户都不会使用给定的 id 值。

如果我想获取集合的所有者类型，我的 SetOwner 表中是否需要一个ownerType 属性？

你当然可以做到这一点。事实上，可能还有用户和学校共有的其他列，您可以将这些列放在超表 SetOwners 中。这涉及到 Martin Fowler 的类表继承模式。

如果我想获取学校或用户的名称（无论是哪种类型），我可以使用单个查询来完成此操作，还是需要两个查询（第一个查询获取类型，第二个查询获取名字）？

您需要加入。如果您从给定的 Set 进行查询并且知道它属于用户（而不是学校），则可以跳过加入 SetOwners 并直接加入 Users。连接不一定必须通过外键。

SELECT u.name FROM Sets s JOIN Users u ON s.SetOwner_id = u.id WHERE ...

如果您不知道给定的集合是否属于用户或学校，则必须对两者进行外连接：

SELECT COALESCE(u.name, sc.name) AS name 
FROM Sets s 
LEFT OUTER JOIN Users u ON s.SetOwner_id = u.id
LEFT OUTER JOIN Schools sc ON s.SetOwner_id = sc.id 
WHERE ...

您知道 SetOwner_id 必须匹配一个或另一个表（用户或学校），但不能同时匹配两者。

Answer 2

需要注意的是：尚不完全清楚数据模型中的集合是什么。

我在质疑你的数据模型。

为什么用户->布景和学校 ->集合必须指向同一个表。如果这些确实是独立的数据集，那么它就不会更加标准化。仅仅因为它们在跟踪的列中有一些相似之处并不意味着它们应该存储在同一个表中。它们在逻辑上是同一件事吗？

只需为学校集和用户集创建单独的表即可。这也将使反向查询变得更容易，因为您不必检查 Sets 表中的空关系来知道它是否真的是 UserSet 还是 SchoolSet。

Answer 3

您必须使用触发器来强制执行此业务规则，因为 MySQL 有但不强制执行 CHECK 约束（在任何引擎上）。