如何在数据库中表示继承?

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

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

在c#等语言中实现这一点很简单，因为您可以创建一个带有Sections集合的Policy，其中Section根据需要继承各种类型的封面。然而，关系数据库似乎不容易做到这一点。

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

  1. 创建一个Policy表，然后创建一个Sections表，其中包含所有可能的变量所需的所有字段，其中大多数字段将为空。

  2. 创建一个Policy表和许多Section表，每个表对应一种封面。

这两种选择似乎都不能令人满意，特别是需要跨所有section编写查询，这将涉及大量连接或大量空检查。

这个场景的最佳实践是什么?

141740 次浏览
小开

第三个选项是创建一个“Policy”表，然后创建一个“SectionsMain”表，用于存储所有在不同类型的节中通用的字段。然后为每种类型的节创建其他只包含不相同字段的表。

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

小开

我倾向于方法#1(一个统一的Section表)，为了有效地检索整个策略及其所有的节(我假设您的系统将会做很多)。

此外，我不知道你使用的是什么版本的SQL Server，但在2008年+ 稀疏列有助于优化性能的情况下，许多值在一个列将为NULL。

最终，您必须决定策略部分有多“相似”。除非它们有很大的不同，否则我认为更规范化的解决方案可能会带来更多的麻烦……但只有你能做决定。：）

小开

根据所提供的信息，我将对数据库进行建模，使其具有以下内容:

政策

  • POLICY_ID(主键)

负债

  • ability_id(主键)
  • POLICY_ID(外键)

属性

  • PROPERTY_ID(主键)
  • POLICY_ID(外键)

.．.等等，因为我希望策略的每个部分都有不同的属性。否则，可能会有一个单独的SECTIONS表，并且除了policy_id之外，还有一个section_type_code…

无论哪种方式，这将允许您支持每个策略的可选部分……

我不明白您对这种方法有什么不满意的地方——这就是存储数据的同时保持引用完整性和不复制数据的方式。术语是“正常化”…

因为SQL是基于SET的，它与过程式/面向对象编程概念相当陌生。需要代码从一个领域转换到另一个领域。通常会考虑orm，但它们在大容量、复杂系统中不能很好地工作。

小开
最佳答案

当提出SQL Entity-Attribute-Value反模式的解决方案时，@Bill Karwin在他的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属性。类型现在由元数据定义:表名。

然而，这种模式也有一些缺点:

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

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

  • 不考虑子类型，搜索所有策略变得困难，并且需要一堆__abc0。

这是如何查询所有的策略，而不管类型:

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变得更容易，因为这可以由基表处理，而不是每个子类型表独立生成它们。

  • 不考虑子类型搜索所有策略现在变得非常简单:不需要__abc0—只需要SELECT * FROM policies。

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

这三个模型的名字来自Martin Fowler的书企业应用程序体系结构模式。

小开

另一种方法是使用INHERITS组件。例如:

CREATE TABLE person (
id int ,
name varchar(20),
CONSTRAINT pessoa_pkey PRIMARY KEY (id)
);


CREATE TABLE natural_person (
social_security_number varchar(11),
CONSTRAINT pessoaf_pkey PRIMARY KEY (id)
) INHERITS (person);




CREATE TABLE juridical_person (
tin_number varchar(14),
CONSTRAINT pessoaj_pkey PRIMARY KEY (id)
) INHERITS (person);

因此，可以在表之间定义继承。

小开

除了Daniel Vassallo解决方案，如果您使用SQL Server 2016+，还有另一种解决方案，我在某些情况下使用，性能不会有相当大的损失。

你可以只创建一个包含公共字段的表，并添加一个包含所有子类型特定字段的JSON字符串列。

我已经测试了这个设计的管理继承，我很高兴，我可以在相关应用程序中使用的灵活性。

小开

或者，可以考虑使用本地支持丰富数据结构和嵌套的文档数据库(如MongoDB)。


提交答案