如何表示数据库中的继承？

Question

我正在考虑如何在 SQL Server 数据库中表示复杂的结构。

考虑一个需要存储一系列对象的详细信息的应用程序，这些对象共享一些属性，但有许多其他不常见的属性。例如，商业保险套餐可能在同一保单记录中包括责任险、机动险、财产险和赔偿险。

在 C# 等中实现这一点很简单，因为您可以创建包含部分集合的策略，其中部分根据各种类型的覆盖的需要继承。然而，关系数据库似乎不允许这样做。

我可以看到有两个主要选择：

1. 创建一个策略表，然后创建一个部分表，其中包含所有可能的变体所需的所有字段，其中大部分为空。

2. 创建一个策略表和多个部分表，每个表对应一种保险。

这两种替代方案似乎都不能令人满意，特别是因为需要跨所有部分编写查询，这将涉及大量联接或大量空检查。

此场景的最佳实践是什么？

Answer 1

第三个选项是创建一个“Policy”表，然后创建一个“SectionsMain”表，用于存储跨不同类型的部分所共有的所有字段。然后为每种类型的部分创建其他表，仅包含不常见的字段。

决定哪个最好主要取决于您有多少字段以及您想要如何编写 SQL。他们都会工作。如果你只有几个字段，那么我可能会选择#1。对于“很多”领域，我倾向于#2 或#3。

Answer 2

@Bill Karwin 在他的 SQL Antipatterns 书，在提出 SQL 实体属性值反模式。这是一个简短的概述：

单表继承（又名每层次结构表继承）：

像第一个选项一样使用单个表可能是最简单的设计。正如您所提到的，许多特定于子类型的属性必须在这些属性不适用的行上被赋予 NULL 值。使用此模型，您将拥有一个策略表，如下所示：

+------+---------------------+----------+----------------+------------------+
| id   | date_issued         | type     | vehicle_reg_no | property_address |
+------+---------------------+----------+----------------+------------------+
|    1 | 2010-08-20 12:00:00 | MOTOR    | 01-A-04004     | NULL             |
|    2 | 2010-08-20 13:00:00 | MOTOR    | 02-B-01010     | NULL             |
|    3 | 2010-08-20 14:00:00 | PROPERTY | NULL           | Oxford Street    |
|    4 | 2010-08-20 15:00:00 | MOTOR    | 03-C-02020     | NULL             |
+------+---------------------+----------+----------------+------------------+

\------ COMMON FIELDS -------/          \----- SUBTYPE SPECIFIC FIELDS -----/

保持设计简单是一个优点，但这种方法的主要问题如下：

• 在添加新的子类型时，您必须更改表以适应描述这些新对象的属性。当您有许多子类型或者您计划定期添加子类型时，这很快就会成为问题。

• 数据库将无法强制执行哪些属性适用，哪些不适用，因为没有元数据来定义哪些属性属于哪些子类型。

• 您也无法对本应强制执行的子类型属性强制执行 NOT NULL。您必须在应用程序中处理这个问题，这通常并不理想。

具体表继承：

解决继承问题的另一种方法是为每个子类型创建一个新表，重复每个表中的所有公共属性。例如：

--// Table: policies_motor
+------+---------------------+----------------+
| id   | date_issued         | vehicle_reg_no |
+------+---------------------+----------------+
|    1 | 2010-08-20 12:00:00 | 01-A-04004     |
|    2 | 2010-08-20 13:00:00 | 02-B-01010     |
|    3 | 2010-08-20 15:00:00 | 03-C-02020     |
+------+---------------------+----------------+
                          
--// Table: policies_property    
+------+---------------------+------------------+
| id   | date_issued         | property_address |
+------+---------------------+------------------+
|    1 | 2010-08-20 14:00:00 | Oxford Street    |   
+------+---------------------+------------------+

这种设计将基本上解决单表方法所确定的问题：

• 现在可以通过 NOT NULL 强制执行强制属性。

• 添加新子类型需要添加新表，而不是向现有表添加列。

• 也不存在为特定子类型设置不适当属性的风险，例如属性策略的 vehicle_reg_no 字段。

• 不需要像单表方法中那样的 type 属性。该类型现在由元数据定义：表名称。

但是这种模型也有一些缺点：

• 公共属性与子类型特定属性混合在一起，没有简单的方法来识别它们。数据库也不知道。

• 定义表时，您必须为每个子类型表重复公共属性。这绝对不是干。

• 无论子类型如何，搜索所有策略都变得很困难，并且需要一堆 UNION。

无论类型如何，您都必须通过以下方式查询所有策略：

SELECT     date_issued, other_common_fields, 'MOTOR' AS type
FROM       policies_motor
UNION ALL
SELECT     date_issued, other_common_fields, 'PROPERTY' AS type
FROM       policies_property;

请注意，添加新的子类型将需要为每个子类型使用附加的 UNION ALL 来修改上述查询。如果忘记此操作，很容易导致应用程序出现错误。

类表继承（又名每个类型的表继承）：

这是@David 在中提到的解决方案另一个答案。您为基类创建一个表，其中包括所有公共属性。然后，您将为每个子类型创建特定的表，其主键也充当基表。示例：

CREATE TABLE policies (
   policy_id          int,
   date_issued        datetime,

   -- // other common attributes ...
);

CREATE TABLE policy_motor (
    policy_id         int,
    vehicle_reg_no    varchar(20),

   -- // other attributes specific to motor insurance ...

   FOREIGN KEY (policy_id) REFERENCES policies (policy_id)
);

CREATE TABLE policy_property (
    policy_id         int,
    property_address  varchar(20),

   -- // other attributes specific to property insurance ...

   FOREIGN KEY (policy_id) REFERENCES policies (policy_id)
);

该解决方案解决了其他两种设计中发现的问题：

• 可以通过 NOT NULL 强制执行强制属性。

• 添加新子类型需要添加新表，而不是向现有表添加列。

• 没有为特定子类型设置不适当属性的风险。

• 不需要 type 属性。

• 现在公共属性不再与子类型特定属性混合。

• 我们终于可以保持干燥了。创建表时无需重复每个子类型表的公共属性。

• 管理策略的自动递增 id 变得更加容易，因为这可以由基表处理，而不是每个子类型表独立生成它们。

• 搜索所有策略（无论子类型如何）现在都变得非常容易：不需要 UNION - 只需 SELECT * FROM 策略。

我认为类表方法在大多数情况下是最合适的。

---

这三个模型的名称来自Martin Fowler一本书企业应用架构模式。