强制完整性的表关系的最佳模式设计

Question

给定一个模型“A”表，它可以有多个子模型“B”，其中“B”将有一个或多个子模型“C”..这听起来很简单，但是我需要对每个“A”强制执行'，任何 'B' 必须具有唯一的 'C' 集合。例如，C 不能是属于同一父级 'A' 的两个 'B' 的子级。但是 'C' 可以是子级考虑到每个“B”的父级“A”都是不同的，多个“B”的数量。

这是否有意义，或者我应该明确我的场景？ 提前欢呼！

请注意，我知道此策略将在应用程序中强制执行，但我不希望数据库不可能处于无效状态。

编辑：大家好，非常好的反馈，所以首先我必须感谢大家与我分享您的知识。

为了澄清这种情况，我将解释该场景，但这里有一些注释：

“A”有零个或多个“B”，“B”隐式地与“A”关联，因此始终是只有一个“A”。 “C”在某种程度上是与许多“B”以及数据库中的其他元素相关联的根实体。

---

真实的故事如下：

这是一个包含许多摘要 (A) 和许多成员 (C) 的网站，一个摘要可以有许多提交内容 (B)，其中提交内容总是有一个或多个关联成员。 这个想法是，提交实际上可以是一种协作，每个成员并不比其他成员拥有更多的“权力”，但将会有一个不同的系统来验证成员如何合作的政策。

因此，根据概要，一个成员只能提交一个提交，而一个提交可以有多个成员（协作者）。

希望这有帮助，但我认为你已经给了我很多帮助！

史蒂夫.

Answer 1

将TableA的ID添加到TableB，并将其添加到主键，对TableB和TableC执行相同的操作。

编辑：

我相信这个答案的第一部分适用于 A 到 B 约束。 然而，我会在 B 和 C 之间放置一个链接表，该表也保存 A 的 PK。 这样你就在 A:B 之间得到了 1:N，然后你的约束就会被强制执行。

Answer 2

我认为您无法通过简单的声明性引用完整性约束来做到这一点。 实施逻辑的最佳方法可能是使用触发器来实现业务约束，并回滚任何违反规则的插入或更新。

Answer 3

我认为您需要 SQL 标准断言，（不幸的是）实际的 DBMS 很大程度上未实现这些断言。

所有答案都同意存在三个主表，分别称为 TableA、TableB 和 TableC，每个表都包含自己的 ID 列：

TableA (A_ID PRIMARY KEY, ...)
TableB (B_ID PRIMARY KEY, ...)
TableC (C_ID PRIMARY KEY, ...)

从问题的描述中不清楚单个 B 值是否可以有多个 A 父条目。 很明显，单个 C 可以有多个 B 父条目。 如果 B 与单个 A 绑定，则 TableB 的设计可以修改为：

TableB (B_ID, ..., A_ID REFERENCES TableA)

如果 B 可以与多个不同的 A 关联，则最好用连接表来表示连接：

A_and_B (A_ID REFERENCES TableA,
         B_ID REFERENCES TableB,
         PRIMARY KEY (A_ID, B_ID)
        )

从描述中也不清楚是否对于与 B 关联的每个 A，与 B 关联的 C 必须相同，或者不同的 A 是否可以引用相同的 B，并且与 A1 的 B 关联的 C 集合可以与关联的 C 集合不同B代表A2。 （当然，如果单个 B 只能与一个 A 关联，则这个问题没有实际意义。）

出于本答案的目的，我将假设任何 B 都与单个 A 关联，因此 TableB 的结构包括 A_ID 作为外键。 由于单个 C 可以与多个 B 相关联，因此相关结构是一个新的连接表：

B_and_C (B_ID REFERENCES TableB,
         C_ID REFERENCES TableC,
         PRIMARY KEY (B_ID, C_ID)
        )

简化（通过省略有关可延迟性和即时性的规则）断言如下所示：

CREATE ASSERTION assertion_name CHECK ( <search_condition> )

因此，一旦我们有了一组设计决策，我们就可以编写一个断言来验证数据。 给定表 TableA、TableB（带有外键 A_ID）、TableC 和 B_and_C，要求是给定 C_ID 在完整 A 中出现的次数为 1。

CREATE ASSERTION only_one_instance_of_c_per_a CHECK
(
     NOT EXISTS (
         SELECT A_ID, COUNT(C_ID)
             FROM TableB JOIN B_and_C USING (C_ID)
             GROUP BY A_ID
             HAVING COUNT(C_ID) > 1
     )
)

[修正：我认为这更多准确：

CREATE ASSERTION only_one_instance_of_c_per_a CHECK
(
     NOT EXISTS (
         SELECT A_ID, C_ID, COUNT(*)
             FROM TableB JOIN B_and_C USING (C_ID)
             GROUP BY A_ID, C_ID
             HAVING COUNT(*) > 1
     )
)

]

连接条件集随表连接方式的其他规则而变化，但总体约束结构保持不变 - 对于特定 A_ID，不得存在对给定 C_ID 的多个引用。

---

在下面的评论中，meanmycode 指出：

我感觉我的设计有缺陷。 我现实世界的逻辑是“B”总是至少有一个孩子“C”。 这是没有意义的，因为“B”必须存在才能附加其子级。
数据库目前允许将“B”附加到“A”，而无需至少有一个“C”..孩子，我将修改“B”，以便它有一个引用其的字段主要子项“C”，以及具有附加“C”的子集合，但现在我有一个集合，该集合还可能包含“B”指定的主“C”，这是......错误的。

是否有一种数据库模式可以推断“一个或多个子项”规则，而不是零个或多个？

我认为你的模型确实有问题。 如果必须已经存在一个引用新创建的 B 的 C，则很难创建 B，特别是如果 C 必须仅引用现有的 B。 我想到了“先有鸡还是先有蛋”这句话。 因此，通常情况下，您允许 B 在这样的上下文中具有零个或多个 C。

您还没有规定 TableB 是否有 A_ID 外键，或者是否有像 A_and_B 这样的链接表。 如果它有外键，那么在创建它引用的 A 之前，您可能无法创建 B。

我不认为在表 B 中包含一个 C ID 是一个好主意 - 它会导致非对称处理（更难的 SQL）。 这也意味着，如果您需要删除该 C，则必须进行更新，以便从其当前所在的表中删除其他 C 引用之一，然后更新 B 记录中的值。 这很混乱，要有礼貌。

我认为您需要修改您的问题来定义您正在查看的实际表结构 - 沿着各种答案中显示的线路； 您可以使用三点来表示其他但不相关的列。 我建议的断言可能必须作为某种触发器来实现 - 它进入 DBMS 特定的符号。

---

从摘要 (A)、提交内容 (B) 和成员 (C) 的修订描述中可以清楚地看出，单个提交内容仅适用于一个摘要，因此提交内容可以有一个简单的外键来标识该摘要是提交内容为了。 并且成员只能就特定简报的一份提交内容进行协作。 将有一个“submission_collaborators”表，其中包含用于标识提交和成员的列，该组合是主键，每列是外键。

Briefs(Brief_ID, ...)
Submissions(Submission_ID, Brief_ID REFERENCES Briefs, ...)
Members(Member_ID, ...)
Submission_Collaborators(Submission_ID REFERENCES Submissions,
                         Member_ID REFERENCES Members,
                         PRIMARY KEY (Submission_ID, Member_ID)
                        )

因此，要求以下查询必须不返回任何行：

SELECT s.brief_id, c.member_id, COUNT(*)
    FROM submissions AS s JOIN submission_collaborators AS c
         ON s.submission_id = c.submission_id
    GROUP BY s.brief_id, c.member_id
    HAVING COUNT(*) > 1

这与我嵌入到 CREATE ASSERTION（第二个变体）中的查询相同。 您还可以挖掘额外的信息（简短标题、提交标题、会员姓名、各种日期等），但问题的核心是显示的查询必须不返回任何数据。

Answer 4

我想我已经在这里捕捉到了你的关系模型； 如果不是，那么我投票赞成取消混淆：

• A [ {AID}, ... ]
• B [ {BID}, AID, ... ]
• C [ {CID}, ... ]
• B_C_Link [ {BID, CID}, AID ]
  • （AID、CID）的附加唯一索引

该表示法使用 {} 主键指示符。 因此，A 可以有多个 B（通过在 B 上放置 AID），B 可以有 C（通过使用多对多表 B_C_Link），并且多个 C 不能属于同一个 A（通过将 AID 添加到多对多表中）。多对表并强制（AID、CID）唯一性。

Answer 5

你所拥有的是三元关系。 您需要做的是有一个表，在其主键中将 A、B 和 C 联系在一起。 由于主键不能重复，这将强制每个 A 和每个 B 只能有一个 C。这将创建您正在寻找的唯一集合。

您将得到以下表结构：

A's({A_ID}, ...)
B's({B_ID}, ...)
C's({C_ID}, ...)
A_B_C_Relation({[A_ID], [B_ID], [C_ID]}, ...)

主键在大括号中，外键在括号中。

请参阅此处了解更多信息。