如何在数据库中表示继承?
我正在考虑如何在 SQL Server 数据库中表示复杂的结构。
考虑一个应用程序,它需要存储一系列对象的详细信息,这些对象共享一些属性,但还有许多不常见的属性。例如,商业保险套餐可能包括同一保单记录中的责任险、汽车险、财产险和赔偿险。
在 C# 等中实现这一点很简单,因为您可以创建一个包含 Sections 集合的 Policy,其中 Section
是根据各种类型的覆盖物的需要继承的。然而,关系数据库似乎并不容易做到这一点。
我可以看到有两个主要选择:
-
创建一个 Policy 表,然后创建一个 Sections 表,其中包含所有必需的字段,用于所有可能的变化,其中大部分为空。
-
创建一个 Policy 表和多个 Section 表,每个表对应一种封面。
这两种选择似乎都不能令​​人满意,特别是因为有必要编写跨所有部分的查询,这将涉及大量连接或大量空检查。
这种情况的最佳实践是什么?
-
实体-
属性-值反模式提出解决方案时,在他的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
像单表方法那样的属性。类型现在由元数据定义:表名。
然而,这种模式也有一些缺点:
-
公共属性与子类型特定属性混合在一起,没有简单的方法来识别它们。数据库也不知道。
-
定义表时,您必须为每个子类型表重复公共属性。那绝对不是DRY。
-
无论子类型如何,搜索所有策略变得困难,并且需要一堆
UNION
s。
无论类型如何,这都是您必须查询所有策略的方式:
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
为每个子类型修改上述查询。如果忘记此操作,这很容易导致您的应用程序出现错误。类表继承(又名表类型继承):
这是在另一个答案中提到的解决方案。您为基类创建一个表,其中包括所有公共属性。然后,您将为每个子类型创建特定的表,其主键也用作基表的外键。例子:
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
s - 只需SELECT * FROM policies
.
我认为类表方法最适合大多数情况。
-