In addition at the Daniel Vassallo solution, if you use SQL Server 2016+, there is another solution that I used in some cases without considerable loss of performance.
You can just create a table with only the common field and add a single column with the JSON string that contains all the subtype specific fields.
I have tested this design for managing inheritance and I am very happy for the flexibility that I can use in the relative application.
因为 SQL 是基于 SET 的，所以它与过程/OO 编程概念和概念相当陌生。需要代码从一个领域转换到另一个领域。人们经常考虑 ORM，但它们在大容量、复杂的系统中效果不佳。
With the information provided, I'd model the database to have the following:
POLICIES
POLICY_ID (primary key)
LIABILITIES
LIABILITY_ID (primary key)
POLICY_ID (foreign key)
PROPERTIES
PROPERTY_ID (primary key)
POLICY_ID (foreign key)
...and so on, because I'd expect there to be different attributes associated with each section of the policy. Otherwise, there could be a single SECTIONS table and in addition to the policy_id, there'd be a section_type_code...
Either way, this would allow you to support optional sections per policy...
I don't understand what you find unsatisfactory about this approach - this is how you store data while maintaining referential integrity and not duplicating data. The term is "normalized"...
Because SQL is SET based, it's rather alien to procedural/OO programming concepts & requires code to transition from one realm to the other. ORMs are often considered, but they don't work well in high volume, complex systems.
I lean towards method #1 (a unified Section table), for the sake of efficiently retrieving entire policies with all their sections (which I assume your system will be doing a lot).
Further, I don't know what version of SQL Server you're using, but in 2008+ Sparse Columns help optimize performance in situations where many of the values in a column will be NULL.
Ultimately, you'll have to decide just how "similar" the policy sections are. Unless they differ substantially, I think a more-normalized solution might be more trouble than it's worth... but only you can make that call. :)
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 来修改上述查询。如果忘记此操作，很容易导致应用程序出现错误。
Single Table Inheritance (aka Table Per Hierarchy Inheritance):
Using a single table as in your first option is probably the simplest design. As you mentioned, many attributes that are subtype-specific will have to be given a NULL value on rows where these attributes do not apply. With this model, you would have one policies table, which would look something like this:
+------+---------------------+----------+----------------+------------------+
| 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 -----/
Keeping the design simple is a plus, but the main problems with this approach are the following:
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.
Concrete Table Inheritance:
Another approach to tackle inheritance is to create a new table for each subtype, repeating all the common attributes in each table. For example:
This design will basically solve the problems identified for the single table method:
Mandatory attributes can now be enforced with NOT NULL.
Adding a new subtype requires adding a new table instead of adding columns to an existing one.
There is also no risk that an inappropriate attribute is set for a particular subtype, such as the vehicle_reg_no field for a property policy.
There is no need for the type attribute as in the single table method. The type is now defined by the metadata: the table name.
However this model also comes with a few disadvantages:
The common attributes are mixed with the subtype specific attributes, and there is no easy way to identify them. The database will not know either.
When defining the tables, you would have to repeat the common attributes for each subtype table. That's definitely not DRY.
Searching for all the policies regardless of the subtype becomes difficult, and would require a bunch of UNIONs.
This is how you would have to query all the policies regardless of the type:
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;
Note how adding new subtypes would require the above query to be modified with an additional UNION ALL for each subtype. This can easily lead to bugs in your application if this operation is forgotten.
Class Table Inheritance (aka Table Per Type Inheritance):
This is the solution that @David mentions in the other answer. You create a single table for your base class, which includes all the common attributes. Then you would create specific tables for each subtype, whose primary key also serves as a foreign key to the base table. Example:
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)
);
This solution solves the problems identified in the other two designs:
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.
I consider the class table approach as the most suitable in most situations.
The 3rd option is to create a "Policy" table, then a "SectionsMain" table that stores all of the fields that are in common across the types of sections. Then create other tables for each type of section that only contain the fields that are not in common.
Deciding which is best depends mostly on how many fields you have and how you want to write your SQL. They would all work. If you have just a few fields then I would probably go with #1. With "lots" of fields I would lean towards #2 or #3.
发布评论
评论（7）
另外，在 Daniel Vassallo 解决方案中，如果您使用 SQL Server 2016+，我在某些情况下使用了另一种解决方案，而不会造成相当大的性能损失。
您可以创建一个仅包含公共字段的表，并使用 JSON 包含所有子类型特定字段的字符串。
我已经测试了这种管理继承的设计，我对可以在相关应用程序中使用的灵活性感到非常高兴。
In addition at the Daniel Vassallo solution, if you use SQL Server 2016+, there is another solution that I used in some cases without considerable loss of performance.
You can just create a table with only the common field and add a single column with the JSON string that contains all the subtype specific fields.
I have tested this design for managing inheritance and I am very happy for the flexibility that I can use in the relative application.
根据提供的信息，我将数据库建模为具有以下内容：
POLICIES
LIABILITIES
PROPERTIES
...等等，因为我希望策略的每个部分都有不同的关联属性。否则，可能会有一个
SECTIONS表，除了policy_id之外，还会有一个section_type_code...无论哪种方式，这将允许您支持每个策略的可选部分...
我不明白您对这种方法有何不满意之处 - 这是您在保持引用完整性而不重复数据的同时存储数据的方式。这个术语是“规范化”...
因为 SQL 是基于 SET 的，所以它与过程/OO 编程概念和概念相当陌生。需要代码从一个领域转换到另一个领域。人们经常考虑 ORM，但它们在大容量、复杂的系统中效果不佳。
With the information provided, I'd model the database to have the following:
POLICIES
LIABILITIES
PROPERTIES
...and so on, because I'd expect there to be different attributes associated with each section of the policy. Otherwise, there could be a single
SECTIONStable and in addition to thepolicy_id, there'd be asection_type_code...Either way, this would allow you to support optional sections per policy...
I don't understand what you find unsatisfactory about this approach - this is how you store data while maintaining referential integrity and not duplicating data. The term is "normalized"...
Because SQL is SET based, it's rather alien to procedural/OO programming concepts & requires code to transition from one realm to the other. ORMs are often considered, but they don't work well in high volume, complex systems.
另一种方法是使用 INHERITS 组件。例如：
因此可以定义表之间的继承。
The another way to do it, is using the
INHERITScomponent. For example:Thus it's possible to define a inheritance between tables.
或者，考虑使用本身支持丰富数据结构和嵌套的文档数据库（例如 MongoDB）。
Alternatively, consider using a document databases (such as MongoDB) which natively support rich data structures and nesting.
我倾向于方法#1（统一的部分表），以便有效地检索整个策略及其所有部分（我假设您的系统会做很多事情）。
此外，我不知道您使用的 SQL Server 版本是什么，但在 2008+ 稀疏列有助于在列中许多值为 NULL 的情况下优化性能。
最终，您必须确定政策部分的“相似度”。除非它们有很大不同，否则我认为更标准化的解决方案可能会带来更多麻烦而不是其价值......但只有您可以做出这样的决定。 :)
I lean towards method #1 (a unified Section table), for the sake of efficiently retrieving entire policies with all their sections (which I assume your system will be doing a lot).
Further, I don't know what version of SQL Server you're using, but in 2008+ Sparse Columns help optimize performance in situations where many of the values in a column will be NULL.
Ultimately, you'll have to decide just how "similar" the policy sections are. Unless they differ substantially, I think a more-normalized solution might be more trouble than it's worth... but only you can make that call. :)
@Bill Karwin 在他的 SQL Antipatterns 书，在提出 SQL 实体属性值反模式。这是一个简短的概述：
单表继承（又名每层次结构表继承）：
如第一个选项中那样使用单个表可能是最简单的设计。正如您所提到的，许多特定于子类型的属性必须在这些属性不适用的行上被赋予 NULL 值。使用此模型，您将拥有一个策略表，它看起来像这样：
保持设计简单是一个优点，但这种方法的主要问题如下：
当涉及到添加新的子类型时，您将必须更改表以适应描述这些新对象的属性。当您有许多子类型或者您计划定期添加子类型时，这很快就会成为问题。
数据库将无法强制哪些属性适用，哪些不适用，因为没有元数据来定义哪些属性属于哪些子类型。
您也不能在子类型的属性上强制执行
NOT NULL，而本应是强制的。您必须在应用程序中处理这个问题，这通常并不理想。具体表继承：
解决继承问题的另一种方法是为每个子类型创建一个新表，重复每个表中的所有公共属性。例如：
此设计将基本上解决单表方法所确定的问题：
现在可以使用
NOT NULL强制执行强制属性。添加新子类型需要添加新表，而不是向现有表添加列。
也不存在为特定子类型设置不适当属性的风险，例如属性策略的
vehicle_reg_no字段。不需要像单表方法中那样的
type属性。该类型现在由元数据定义：表名称。然而，该模型也有一些缺点：
公共属性与子类型特定属性混合在一起，并且没有简单的方法来识别它们。数据库也不知道。
定义表时，您必须为每个子类型表重复公共属性。这绝对不是干。
无论子类型如何，搜索所有策略都会变得很困难，并且需要一堆
UNION。这是您必须查询所有策略（无论类型如何）的方式：
请注意，添加新子类型将需要使用每个子类型的附加
UNION ALL来修改上述查询。如果忘记此操作，很容易导致应用程序出现错误。类表继承（又名每类型表继承）：
这是@David 在另一个答案中提到。您为基类创建一个表，其中包括所有公共属性。然后，您将为每个子类型创建特定的表，其主键也充当基表。示例：
此解决方案解决了其他两种设计中发现的问题：
可以使用
NOT NULL强制执行强制属性。添加新子类型需要添加新表，而不是向现有表添加列。
没有为特定子类型设置不适当属性的风险。
不需要
type属性。现在公共属性不再与子类型特定属性混合。
我们终于可以保持干燥了。创建表时无需重复每个子类型表的公共属性。
管理策略的自动递增
id变得更加容易，因为这可以由基表处理，而不是每个子类型表独立生成它们。无论子类型如何，搜索所有策略现在都变得非常容易：不需要
UNION- 只需SELECT * FROM 策略。我认为类表方法在大多数情况下是最合适的。
这三个模型的名称来自 Martin Fowler 的书企业应用程序架构模式。
@Bill Karwin describes three inheritance models in his SQL Antipatterns book, when proposing solutions to the SQL Entity-Attribute-Value antipattern. This is a brief overview:
Single Table Inheritance (aka Table Per Hierarchy Inheritance):
Using a single table as in your first option is probably the simplest design. As you mentioned, many attributes that are subtype-specific will have to be given a
NULLvalue on rows where these attributes do not apply. With this model, you would have one policies table, which would look something like this:Keeping the design simple is a plus, but the main problems with this approach are the following:
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 NULLon attributes of a subtype that should be mandatory. You would have to handle this in your application, which in general is not ideal.Concrete Table Inheritance:
Another approach to tackle inheritance is to create a new table for each subtype, repeating all the common attributes in each table. For example:
This design will basically solve the problems identified for the single table method:
Mandatory attributes can now be enforced with
NOT NULL.Adding a new subtype requires adding a new table instead of adding columns to an existing one.
There is also no risk that an inappropriate attribute is set for a particular subtype, such as the
vehicle_reg_nofield for a property policy.There is no need for the
typeattribute as in the single table method. The type is now defined by the metadata: the table name.However this model also comes with a few disadvantages:
The common attributes are mixed with the subtype specific attributes, and there is no easy way to identify them. The database will not know either.
When defining the tables, you would have to repeat the common attributes for each subtype table. That's definitely not DRY.
Searching for all the policies regardless of the subtype becomes difficult, and would require a bunch of
UNIONs.This is how you would have to query all the policies regardless of the type:
Note how adding new subtypes would require the above query to be modified with an additional
UNION ALLfor each subtype. This can easily lead to bugs in your application if this operation is forgotten.Class Table Inheritance (aka Table Per Type Inheritance):
This is the solution that @David mentions in the other answer. You create a single table for your base class, which includes all the common attributes. Then you would create specific tables for each subtype, whose primary key also serves as a foreign key to the base table. Example:
This solution solves the problems identified in the other two designs:
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
typeattribute.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
idfor 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 aSELECT * FROM policies.I consider the class table approach as the most suitable in most situations.
The names of these three models come from Martin Fowler's book Patterns of Enterprise Application Architecture.
第三个选项是创建一个“Policy”表，然后创建一个“SectionsMain”表，用于存储跨不同类型的部分所共有的所有字段。然后为每种类型的部分创建其他表，仅包含不常见的字段。
决定哪个最好主要取决于您有多少字段以及您想要如何编写 SQL。他们都会工作。如果你只有几个字段，那么我可能会选择#1。对于“很多”领域，我会倾向于#2 或#3。
The 3rd option is to create a "Policy" table, then a "SectionsMain" table that stores all of the fields that are in common across the types of sections. Then create other tables for each type of section that only contain the fields that are not in common.
Deciding which is best depends mostly on how many fields you have and how you want to write your SQL. They would all work. If you have just a few fields then I would probably go with #1. With "lots" of fields I would lean towards #2 or #3.