我正在考虑如何在SQL Server数据库中表示一个复杂的结构。
考虑这样一个应用程序，它需要存储一系列对象的详细信息，这些对象共享一些属性，但有许多其他不常见的属性。例如，一个商业保险包可能在同一份保单记录中包括责任、汽车、财产和赔偿。
在c#等语言中实现这一点很简单，因为您可以创建一个带有Sections集合的Policy，其中Section根据需要继承各种类型的封面。然而，关系数据库似乎不容易做到这一点。
我可以看到有两个主要的选择:
创建一个Policy表，然后创建一个Sections表，其中包含所有可能的变量所需的所有字段，其中大多数字段将为空。 创建一个Policy表和许多Section表，每个表对应一种封面。
这两种选择似乎都不能令人满意，特别是需要跨所有section编写查询，这将涉及大量连接或大量空检查。
这个场景的最佳实践是什么?
当前回答
第三个选项是创建一个“Policy”表，然后创建一个“SectionsMain”表，用于存储所有在不同类型的节中通用的字段。然后为每种类型的节创建其他只包含不相同字段的表。
决定哪种是最好的主要取决于您有多少字段以及您想如何编写SQL。它们都会起作用。如果你只有几个字段，那么我可能会选择#1。对于“很多”领域，我会倾向于#2或#3。
其他回答
当提出SQL实体-属性-值反模式的解决方案时，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 -----/
保持设计简单是一个优点，但这种方法的主要问题如下:
When it comes to adding new subtypes, you would have to alter the table to accommodate the attributes that describe these new objects. This can quickly become problematic when you have many subtypes, or if you plan to add subtypes on a regular basis. The database will not be able to enforce which attributes apply and which don't, since there is no metadata to define which attributes belong to which subtypes. You also cannot enforce NOT NULL on attributes of a subtype that should be mandatory. You would have to handle this in your application, which in general is not ideal.
具体表继承:
处理继承的另一种方法是为每个子类型创建一个新表，重复每个表中的所有公共属性。例如:
--// 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。
这是如何查询所有的策略，而不管类型:
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)
);
这个解决方案解决了其他两个设计中发现的问题:
Mandatory attributes can be enforced with NOT NULL. Adding a new subtype requires adding a new table instead of adding columns to an existing one. No risk that an inappropriate attribute is set for a particular subtype. No need for the type attribute. Now the common attributes are not mixed with the subtype specific attributes anymore. We can stay DRY, finally. There is no need to repeat the common attributes for each subtype table when creating the tables. Managing an auto incrementing id for the policies becomes easier, because this can be handled by the base table, instead of each subtype table generating them independently. Searching for all the policies regardless of the subtype now becomes very easy: No UNIONs needed - just a 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);
因此，可以在表之间定义继承。
根据所提供的信息，我将对数据库进行建模，使其具有以下内容:
政策
POLICY_ID(主键)
负债
ability_id(主键) POLICY_ID(外键)
属性
PROPERTY_ID(主键) POLICY_ID(外键)
.．.等等，因为我希望策略的每个部分都有不同的属性。否则，可能只有一个SECTIONS表，除了policy_id之外，还有一个section_type_code…
无论哪种方式，这将允许您支持每个策略的可选部分……
我不明白您对这种方法有什么不满意的地方——这就是存储数据的同时保持引用完整性和不复制数据的方式。术语是“正常化”…
因为SQL是基于SET的，它与过程式/OO编程概念相当陌生，并且需要代码从一个领域转换到另一个领域。通常会考虑orm，但它们在大容量、复杂系统中不能很好地工作。
除了Daniel Vassallo解决方案，如果您使用SQL Server 2016+，还有另一种解决方案，我在某些情况下使用，性能不会有相当大的损失。
您可以只使用公共字段创建一个表，并使用包含所有子类型特定字段的JSON字符串添加一个列。
我已经测试了这个设计的管理继承，我很高兴，我可以在相关应用程序中使用的灵活性。
我倾向于方法#1(一个统一的Section表)，为了有效地检索整个策略及其所有的节(我假设您的系统将会做很多)。
此外，我不知道你使用的是什么版本的SQL Server，但在2008年+稀疏列有助于优化性能的情况下，许多值在一个列将为NULL。
最终，您必须决定策略部分有多“相似”。除非它们有很大的不同，否则我认为更规范化的解决方案可能会带来更多的麻烦……但只有你能做决定。：）
