Wie stellt man die Vererbung in der Datenbank dar?

Question

Ich denke darüber nach, wie man komplexe Strukturen in einer SQL Server-Datenbank darstellen kann.

Stellen Sie sich eine Anwendung vor, die Details zu einer Reihe von Objekten speichern muss, die einige Eigenschaften gemeinsam haben, aber viele andere ungewöhnliche Eigenschaften haben. Ein gewerbliches Versicherungspaket könnte beispielsweise eine Haftpflicht-, Kfz-, Sach- und Haftpflichtversicherung im selben Versicherungsdatensatz umfassen.

Dies lässt sich beispielsweise in C# einfach erreichen, da Sie Richtlinien erstellen können, die Sammlungen von Teilen enthalten, wobei Teile nach Bedarf für verschiedene Arten von Überschreibungen geerbt werden. Allerdings scheinen relationale Datenbanken dies nicht zu ermöglichen.

Ich sehe, dass es zwei Hauptoptionen gibt:

1. Erstellen Sie eine Strategietabelle und dann eine Teiltabelle mit allen erforderlichen Feldern für alle möglichen Variationen, von denen die meisten leer sind.

2. Erstellen Sie eine Policentabelle und mehrere Teiltabellen, wobei jede Tabelle einer Versicherungsart entspricht.

Beide Alternativen scheinen unbefriedigend zu sein, insbesondere da die Abfrage über alle Teile hinweg geschrieben werden müsste, was entweder viele Verknüpfungen oder viele Nullprüfungen erfordern würde.

Was sind die Best Practices für dieses Szenario?

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一本书企业应用架构模式。