棘手的关系数据库设计问题

Question

我有一个棘手的问题，我已经困扰了几天，但找不到最佳解决方案。

这些是我的表：

• site
• site_node
• page

站点节点表包含表示层次结构的节点列表（使用嵌套集）。 每个节点必须有一个或多个关联的页面。 每个站点必须有一个关联的错误页面和一个未找到页面。

因此，页面必须要么属于节点，要么属于错误或未找到页面的站点。 我目前正在尝试的解决方案是：

1. 页表上的parentType和parentId字段，其中类型可以是“node”，“site_error”或“site_notFound”，id将是站点或节点id（以与方式）。
2. 页表上的nodeId字段可以为null，然后是站点表上的errorPageId和notFoundPageId字段。

选项 #1 确保每一页都属于一个且仅一个其他实体，尽管这种关系实际上无法强制执行，因为 ParentId 字段可以指向多个位置。

选项 #2 更清晰，但它基本上是说该网站“属于”两个错误页面和未找到页面，这可能是不好的做法。

有什么想法或建议吗？
谢谢，
杰克

Answer 1

为错误或未找到的页面创建虚拟站点节点。 您可以根据第一个选项将它们标记为特定类型的节点。 这将使创建通用处理程序机制变得更加容易。 它还将使连接变得更简单，这将有助于提高数据库查询性能。 此外，它允许您添加更多类型的“特殊”页面（可能是登录屏幕）或使其可配置，而无需修改数据库架构。

Answer 2

选项 #1 确保每个页面
只属于一个人
实体，尽管关系不能
实际上被强制作为parentId
字段可以指向多个
地点。

正确的。 就关系理论而言，问题在于您的“parentId”列违反了第三条正常形式，因为它的含义根据 parentType（非键列）中的值每行而变化。

如果单列每行可能包含某人的电话号码或他们的生日（具体取决于其他一些标志），那么您就不会拥有正确设计的数据库。 这是关于这个人的两个不同的事实，他们每个人都应该有自己的专栏。 同样，将 site_id 或 node_id 存储在单个列中也会出现相同的问题。

表明这是一个有缺陷的设计的另一个线索是，您无法声明外键约束来指向两个引用表中的任何一个。

选项 #2 更干净，但它
基本上就是说该网站
“属于”两个错误而不是
找到了页面，这可能很糟糕
练习。

我明白你为什么这么说，因为属于类 Rails 框架中的约定。 但这些都是约定俗成的。 它们不一定是外键可以建模的唯一关系。 您可以使一个实体以有一个关系精确引用另一个实体。 在这种情况下，外键会反转方向。

我想说的是，从逻辑上讲，错误页面和未找到页面属于该网站，而不是相反。 使它们成为强制性的方法是让另一个实体引用这些页面，并对这些引用应用 NOT NULL 约束。 这就是你所描述的。

CREATE TABLE site (
  . . .
  error_page_id     INT NOT NULL,
  notfound_page_id  INT NOT NULL,
  FOREIGN KEY (error_page_id)    REFERENCES pages (page_id),
  FOREIGN KEY (notfound_page_id) REFERENCES pages (page_id)
);

这满足了您的迫切需要，它是可执行的，并且是正常形式。

---

@NXC 建议为错误和未找到页面创建虚拟节点。 尽管这允许将这些节点存储在节点层次结构中，但它无法强制站点必须具有这些页面。 也就是说，可以在不引用这些节点的情况下存储站点。

@Tony Andrews 建议在每个页面中存储两列，site_id 和 site_node_id，并添加 CHECK 约束以确保其中一个恰好为非 NULL。 这似乎比 parent_id/parent_type 选项更好，但它仍然没有强制要求每个站点都必须有一个错误和未找到页面。

Answer 3

另一种选择是有 2 列 site_id 和 site_node_id，如下所示：

create table pages
 ( page_id ... primary key
 , site_id references sites
 , site_node_id references site_nodes
 , ...
 , constraint site_or_node check (  site_id is null and site_node_id is not null
                                 or site_id is not null and site_node_id is null
                                 )
 );

现在，您可以使用引用完整性来确保每个页面属于一个站点或一个节点，而不是同时属于两者。

Answer 4

选项 2 更有意义，并且在以后出现更多并发症时可以挽救您的大脑。 站点与错误/未找到页面的一对一关系使其非常适合外键约束。

Answer 5

选项 1 的修改。

包括两个单独的列：ParentNodeID 和 ParentSiteID。 根据情况，将这两列之一保留为 NULL。 现在您仍然可以为每个外键声明一个外键（引用）约束。

我不太明白 SiteNotFound 的情况。 在这种情况下，您可以将两个外键都保留为 NULL 吗？

您的加入和搜索将更加简单。 您还将遵守 1NF。 这并非巧合。

您的选项 1 将来自不同域的值组合到一个字段中。 这是糟糕的现场设计，IIRC 违反了 1NF。