sql - 通用数据库表设计

Question

只是想找出针对以下场景设计表格的最佳方法:

我的系统中有多个区域(文档、项目、组和客户)，每个区域都可以记录针对它们的评论。

我的问题是我应该有一张这样的表:

CommentID
DocumentID
ProjectID
GroupID
ClientID
etc

其中只有一个 id 会有数据，其余的将为 NULL，或者我应该有一个单独的 CommentType 表并拥有如下所示的评论表:

CommentID
CommentTypeID
ResourceID (this being the id of the project/doc/client)
etc

我的想法是，从索引的角度来看，选项 2 会更有效。这是正确的吗？

Answer 1

选项 2 对于关系数据库不是一个好的解决方案。它被称为多态关联(正如 @Daniel Vassallo 所提到的)，它打破了关系的基本定义。

例如，假设两个不同行的 ResourceId 均为 1234。这些代表相同的资源吗？这取决于这两行的 CommentTypeId 是否相同。这违反了关系中类型的概念。请参阅SQL and Relational Theory作者:C. J. Date 了解更多详情。

表明这是一个损坏的设计的另一个线索是您无法为 ResourceId 声明外键约束，因为它可能指向多个表中的任何一个。如果您尝试使用触发器或其他方式强制引用完整性，您会发现每次添加新类型的可评论资源时都需要重写触发器。

我会用 @mdma 简要提到(但随后忽略)的解决方案来解决这个问题:

CREATE TABLE Commentable (
  ResourceId INT NOT NULL IDENTITY,
  ResourceType INT NOT NULL,
  PRIMARY KEY (ResourceId, ResourceType)
);

CREATE TABLE Documents (
  ResourceId INT NOT NULL,
  ResourceType INT NOT NULL CHECK (ResourceType = 1),
  FOREIGN KEY (ResourceId, ResourceType) REFERENCES Commentable
);

CREATE TABLE Projects (
  ResourceId INT NOT NULL,
  ResourceType INT NOT NULL CHECK (ResourceType = 2),
  FOREIGN KEY (ResourceId, ResourceType) REFERENCES Commentable
);

现在每种资源类型都有自己的表，但是序列主键是由Commentable唯一分配的。给定的主键值只能由一种资源类型使用。

CREATE TABLE Comments (
  CommentId INT IDENTITY PRIMARY KEY,
  ResourceId INT NOT NULL,
  ResourceType INT NOT NULL,
  FOREIGN KEY (ResourceId, ResourceType) REFERENCES Commentable
);

现在评论引用可评论的资源，并强制执行引用完整性。给定的注释只能引用一种资源类型。不可能出现异常或资源 ID 冲突。

我在演讲中详细介绍了多态关联 Practical Object-Oriented Models in SQL和我的书SQL Antipatterns .