您应该使用单表继承还是在视图中联合的多个表？

Question

假设您有一个注释表。该注释可以是关于特定帐户、订单行或订单的。

• 有关帐户的注释不适用于任何特定订单行或订单。
• 有关订单行的注释也适用于父订单和附加到订单的帐户。
• 订单上的注释也适用于附加帐户，但不适用于订单行。

注释表

[Id]          [int] IDENTITY(1,1) NOT NULL
[NoteTypeId]  [smallint] NOT NULL
[AccountId]   [int] NULL
[OrderId]     [int] NULL
[OrderLineId] [int] NULL,
[Note]        [varchar](300) NOT NULL

这个想法是，如果我查看客户，我可以看到以某种方式相关的所有注释。最初，我为上述每个内容创建了一个注释表，并在视图中将它们联合起来。 这里的问题在于编辑/删除记录。可以在特定项目上或在帐户或订单的通用注释视图中编辑/删除注释。这种方法使这变得更加困难。

然后我切换到 单表继承 模式。我的注释表的 AccountId、OrderId 和 OrderLineId 的值可为空。我还添加了 NoteTypeId 来明确标识记录。管理更新/删除场景变得更加容易。

我有一些问题&这种方法仍然存在疑问。

• 完整性 - 尽管可以在 SQL 和/或代码中设置复杂的约束，但大多数 DBA 不喜欢 STI 方法。
• 一堆空值的想法是有争议的（尽管我相信 SQL 2008 中的性能已经基于空值的存储而得到了改进）
• RDBMS 中的表不必代表代码中的对象。表中的规范化并不意味着该表必须是唯一的对象。我相信前两句话是真的，你说呢？

这里讨论了一些。 是否过度使用数据库中可为空的列是“代码味道”吗？ 我不得不说我同意 Ian 的观点，但我也想要一些相反的观点。

Answer 1

最初我[创建了]单独的笔记
上述各项的表格
在视图中对它们进行联合。

这让我想知道您是否考虑过使用没有可为 NULL 列的多表结构，其中每个注释都会获得唯一的 ID，无论类型如何。您可以在查询中以“单表继承”（或类似形式）显示数据，而无需使用 UNION。

以下是建议的结构。 以使不同类型更清晰、更易于阅读（无论如何，您都没有枚举 INTEGER 值）：

CREATE TABLE Notes
(
 Id INTEGER IDENTITY(1,1) NOT NULL UNIQUE, 
 NoteType VARCHAR(11) NOT NULL
    CHECK (NoteType IN ('Account', 'Order', 'Order line')), 
 Note VARCHAR(300) NOT NULL, 
 UNIQUE (Id, NoteType)
);

CREATE TABLE AccountNotes
(
 Id INTEGER NOT NULL UNIQUE, 
 NoteType VARCHAR(11) 
    DEFAULT 'Account' 
    NOT NULL
    CHECK (NoteType = 'account'),
 FOREIGN KEY (Id, NoteType)
    REFERENCES Notes (Id, NoteType)
       ON DELETE CASCADE, 
 AccountId INTEGER NOT NULL
    REFERENCES Accounts (AccountId)
);

CREATE TABLE OrderNotes
(
 Id INTEGER NOT NULL UNIQUE, 
 NoteType VARCHAR(11) 
    DEFAULT 'Order'
    NOT NULL
    CHECK (NoteType = 'Order'),
 FOREIGN KEY (Id, NoteType)
    REFERENCES Notes (Id, NoteType)
       ON DELETE CASCADE, 
 OrderId INTEGER NOT NULL
    REFERENCES Orders (OrderId)
);

CREATE TABLE OrderLineNotes
(
 Id INTEGER NOT NULL UNIQUE, 
 NoteType VARCHAR(11) 
    DEFAULT 'Order line'
    NOT NULL
    CHECK (NoteType = 'Order line'),
 FOREIGN KEY (Id, NoteType)
    REFERENCES Notes (Id, NoteType)
       ON DELETE CASCADE, 
 OrderLineId INTEGER NOT NULL
    REFERENCES OrderLines (OrderLineId)
);

我已将 NoteTypeId 更改为 VARCHAR ， “单表继承”结构（即所有JOIN，没有UNION）：

SELECT N1.Id, N1.NoteType, N1.Note, 
       AN1.AccountId, 
       ON1.OrderId, 
       OLN1.OrderLineId
  FROM Notes AS N1
       LEFT OUTER JOIN AccountNotes AS AN1
          ON N1.Id = AN1.Id
       LEFT OUTER JOIN OrderNotes AS ON1
          ON N1.Id = ON1.Id
       LEFT OUTER JOIN OrderLineNotes AS OLN1
          ON N1.Id = OLN1.Id;

考虑上述结构具有完整的数据完整性约束。要使用“单表继承”结构执行相同的操作，需要更多的 CHECK 约束，其中包含许多可空列的条件，例如，

CHECK (
       (
        AccountId IS NOT NULL
        AND OrderId IS NULL
        AND OrderLineId IS NULL
       )
       OR
       (
        AccountId IS NULL
        AND OrderId IS NOT NULL
        AND OrderLineId IS NULL
       )
       OR
       (
        AccountId IS NULL
        AND OrderId IS NULL
        AND OrderLineId IS NOT NULL
       )
      );

CHECK (
       (
        NoteType = 'Account'
        AND AccountId IS NOT NULL
       )
       OR
       (
        NoteType = 'Order'
        AND OrderId IS NOT NULL
       )
       OR 
       (
        NoteType = 'Order line'
        AND OrdereLineId IS NOT NULL
       )
      );

etc etc

我敢打赌，大多数使用“单表继承”的应用程序开发人员不会如果他们突然想到要创建这些数据完整性约束，那么他们会费心去创建这些约束（这并不是说听起来很粗鲁，只是对于我们这些更关心“后端”而不是“前端”的人来说，优先级有所不同:)

Answer 2

尽管可以在 SQL 和/或代码中设置复杂的约束，但大多数 DBA 不喜欢 STI 方法。

因为您需要额外的逻辑（CHECK 约束或触发器）来实现注释仅引用实体之一（帐户、订单、订单行）的业务规则。

在每个实体和注释表之间实现多对多表更具可扩展性。

• 不需要 ALTER TABLE 语句添加另一个可为空的外键（有列限制，但大多数人都可能达到它）
• 单个注释记录可以与多个实体关联
• 如果新实体对现有记录没有影响&添加多对多表

Answer 3

看来 STI 在您的情况下可以正常工作？如果我正确地阅读了您的要求，实体继承将是一个链：

Note <- AccountNote(AccountId) <- AccountAndOrderNote(OrderId) <-AccountAndOrderAndOrderLineNote (OrderLineId)

完整性：
确定不是问题吗？ AccountId、OrderId 和 OrderLineId 中的每一个都可以 FK 到它们各自的表（或者为 NULL）
另一方面，如果您删除了 AccountId、OrderId 和 OrderLineId（顺便说一句，我不推荐！）而只删除了 ObjectId 和 NoteTypeId，那么您将无法添加 RI，并且在类型 Join 时会遇到非常混乱的情况。

表现：
既然你说 AccountId 必须始终存在，我想它可能是非空的，并且由于 OrderLine 不能没有 Order 而存在，所以 (AccountId, OrderId) 或 (AccountId, OrderId, OrderLineId) 的索引似乎是有意义的（取决于平均 #OrderLines 每个订单的可选择性与窄度权衡）

但是 OMG Ponies 对于混乱的 ALTER TABLE 的看法是正确的，可以将其扩展到新的注释类型，并且如果新注释不是源自帐户，则索引将引起麻烦。

华泰