数据库设计中类似继承的东西

Question

假设您正在设置一个数据库来存储各种车辆的碰撞测试数据。您想要存储速艇、汽车和卡丁车的碰撞测试数据。

您可以创建三个单独的表：SpeedboatTests、CarTests和GokartTests。但是，许多列在每个表中都会是相同的（例如执行测试的人员的员工ID，碰撞方向（前面、侧面、后面）等）。然而，许多列将是不同的，因此您不希望仅将所有测试数据放在一个表中，因为对于速艇来说，将始终有很多列为空，对于汽车来说，将始终有很多列为空，而对于卡丁车来说，也将始终有很多列为空。

假设您还想存储一些与测试直接无关的信息（例如正在测试的物品的设计者的员工ID）。这些列似乎不适合放在“Tests”表中，特别是因为它们将重复出现在同一车辆上的所有测试中。

下面是一种可能的表格排列方式，以便您了解涉及的问题。

Speedboats
id | 关于速艇但不是测试的列1 | 关于速艇但不是测试的列2
Cars
id | 关于汽车但不是测试的列1 | 关于汽车但不是测试的列2
Gokarts
id | 关于卡丁车但不是测试的列1 | 关于卡丁车但不是测试的列2
Tests
id | 类型 | 类型中的ID | 所有测试相关的列1 | 所有测试相关的列2
（“类型中的ID”将引用下面三个表之一的ID列，具体取决于“类型”的值）
SpeedboatTests
id | 速艇ID | 关于速艇测试的列1 | 关于速艇测试的列2
CarTests
id | 汽车ID | 关于汽车测试的列1 | 关于汽车测试的列2
GokartTests
id | 卡丁车ID | 关于卡丁车测试的列1 | 关于卡丁车测试的列2

这种结构的优点/缺点是什么？实现这样一个结构的首选方式是什么？

这种结构的好处在于它可以避免数据冗余和数据不一致，并且对于每种类型的车辆，都只需要存储与其相关的测试数据。缺点是查询可能会变得更加复杂，并且如果您需要添加其他类型的车辆，则必须修改架构。

更好的解决方案是使用关系数据库中的"继承"或"子类化"，即创建一个父表来存储通用数据，然后为每个特定类型的车辆创建一个子表。这样可以避免在每个子表中重复相同的列，并且添加其他类型的车辆时也不需要修改数据库结构。

如果还有一些适用于所有车辆的信息，您希望将其放在一个车辆表中，那么CarTests表会是这样的吗？

id | vehicle_id | ...
而Vehicles表则如下：
id | type | id_in_type
（其中id_in_type指向一个速艇、汽车或卡丁车的ID）

看起来这只是变得更加混乱了。应该如何设置这样的内容呢？

- Daniel Straight

---

可能是如何在数据库中有效地建模继承关系？的重复问题。 - Musa Haidari

Answer 1

type和id_in_type的设计被称为多态关联。该设计违反了多个规范化原则。最重要的是，你无法声明真正的外键约束条件，因为id_in_type可能引用几个表中的任何一个。

以下是定义表的更好方式：

• 创建一个抽象表Vehicles，为所有车辆子类型和车辆测试提供抽象参考点。
• 每个车辆子类型都有一个主键，不自动递增，而是引用Vehicles。
• 每个测试子类型都有一个主键，不自动递增，而是引用Tests。
• 每个测试子类型还具有对应车辆子类型的外键。

以下是示例DDL：

CREATE TABLE Vehicles (
 vehicle_id INT AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE Speedboats (
 vehicle_id INT PRIMARY KEY,
 col_about_speedboats_but_not_tests1 INT,
 col_about_speedboats_but_not_tests2 INT,
 FOREIGN KEY(vehicle_id) REFERENCES Vehicles(vehicle_id)
);

CREATE TABLE Cars (
 vehicle_id INT PRIMARY KEY,
 col_about_cars_but_not_tests1 INT,
 col_about_cars_but_not_tests2 INT,
 FOREIGN KEY(vehicle_id) REFERENCES Vehicles(vehicle_id)
);

CREATE TABLE Gokarts (
 vehicle_id INT PRIMARY KEY,
 col_about_gokarts_but_not_tests1 INT,
 col_about_gokarts_but_not_tests2 INT,
 FOREIGN KEY(vehicle_id) REFERENCES Vehicles(vehicle_id)
);

CREATE TABLE Tests (
 test_id INT AUTO_INCREMENT PRIMARY KEY,
 col_about_all_tests1 INT,
 col_about_all_tests2 INT
);

CREATE TABLE SpeedboatTests (
 test_id INT PRIMARY KEY,
 vehicle_id INT NOT NULL,
 col_about_speedboat_tests1 INT,
 col_about_speedboat_tests2 INT,
 FOREIGN KEY(test_id) REFERENCES Tests(test_id),
 FOREIGN KEY(vehicle_id) REFERENCES Speedboats(vehicle_id)
);

CREATE TABLE CarTests (
 test_id INT PRIMARY KEY,
 vehicle_id INT NOT NULL,
 col_about_car_tests1 INT,
 col_about_car_tests2 INT,
 FOREIGN KEY(test_id) REFERENCES Tests(test_id),
 FOREIGN KEY(vehicle_id) REFERENCES Cars(vehicle_id)
);

CREATE TABLE GokartTests (
 test_id INT PRIMARY KEY,
 vehicle_id INT NOT NULL,
 col_about_gokart_tests1 INT,
 col_about_gokart_tests2 INT,
 FOREIGN KEY(test_id) REFERENCES Tests(test_id),
 FOREIGN KEY(vehicle_id) REFERENCES Gokarts(vehicle_id)
);

你可以选择声明Tests.vehicle_id，它引用Vehicles.vehicle_id并且去掉每个测试子类型表中的vehicle_id外键，但这样会允许异常情况，例如引用gokart id的speedboat测试。

- Bill Karwin

---

请删除或深埋除了提到Martin Fowler的回答之外的所有其他答案... 天哪.. - Rafa

这是类表继承方法。其他选择在此处列举：https://dev59.com/lHA65IYBdhLWcg3w4C6j#3579462 - oligofren

@Fx32 我认识的每个有经验的数据库开发人员都会避免使用外键。 - Bill Karwin

在我看来，最好使用一个安全、规范化且“有点慢”的主数据库来写入“绝对真理”，然后在性能重要时将这些数据非规范化到缓存中（如elastic、redis、bigquery等）。为了性能而妥协单一真相的来源是我不愿意做的事情。 - okdewit

那些为了稍微的短暂表现而放弃基本诚信的人，既不配拥有诚信也不配拥有表现。 - Neil

Answer 2

我认为在将继承层次结构映射到数据库表时，Martin Fowler在他的书《企业应用架构模式》中很好地阐述了替代方案。

http://martinfowler.com/eaaCatalog/singleTableInheritance.html

http://martinfowler.com/eaaCatalog/classTableInheritance.html

http://martinfowler.com/eaaCatalog/concreteTableInheritance.html

如果子类的附加字段/列数量较少，则单个表继承通常是最简单的处理方式。

如果您使用PostgreSQL作为数据库，并且愿意将自己与特定于数据库的功能绑定，它支持直接使用表继承:

http://www.postgresql.org/docs/8.3/static/ddl-inherit.html

- hallidave

---

我要补充的是，特别是针对原问题中提到的混乱情况，外键应该从具体车型指向抽象的车辆表。例如：快艇（vehicle_id FK，speedboat_specific_column1等）。 - Robin

Answer 3

我会将其拆分成不同的表格，例如车辆（ID，类型等），车辆属性（）VehicleID，AttributeID，Value），碰撞测试信息（VehicleID，CrashtestID，Date等）。CrashTestAttributes（CrashTestID，AttributeID，Value）

或者，而不是属性，可以为应记录的每组类似细节单独创建表格。

- ck

---

那是实体-属性-值设计，对于OP的情况来说有些过度设计了。 - Bill Karwin

Answer 4

如果你正在使用Python的SQLAlchemy，一个对象关系映射器，你可以配置继承层次结构如何映射到数据库表。对象关系映射器对于驯服繁琐的SQL非常有用。

对于你的问题，垂直表可能是一个很好的选择。不要把所有东西都存储在一个模式中，而是将对象的类型和主键存储在一张表中，将每个对象的键/值元组存储在另一张表中。如果你真的在存储汽车测试数据，这种设置将使添加新类型的结果变得更加容易。

- joeforker

Answer 5

在谷歌上搜索“gen-spec关系建模”。您会发现有关如何设置存储广义实体属性（OO程序员可能称为超类）的表格，每个专用实体（子类）的单独表格以及如何使用外键将其全部链接在一起的文章。

在我看来，最好的文章是从ER建模的角度讨论gen-spec。如果您知道如何将ER模型转换为关系模型，然后转换为SQL表格，那么一旦他们向您展示如何在ER中建模gen-spec，您就会知道该怎么做。

如果您只在谷歌上搜索“gen-spec”，您会看到大多数是面向对象的，而不是面向关系的。只要您知道如何克服对象关系阻抗不匹配，那些东西也可能很有用。

- Walter Mitty

---

如果您能提供一些直接链接，那就太好了。 - JARC

这只是Class Table Approach（如接受的答案和引用Fowler的答案中提到的那样）。 - oligofren

Answer 6

你的设计合理，符合正确的规范化规则。你可能缺少一个车辆表，其中包含车辆ID和类型（即Speedboats、Cars和Gokarts的“父级”，在那里你可以保存像“DesignedByUserId”这样的东西）。车辆表和Speedboats表之间是一对一的关系，而车辆表和Speedboat/Cars/GoKarts之间是1对1的关系（即一个车辆只能有1条Speedboat、Cars或GoKarts的记录）...尽管大多数数据库没有提供易于执行此操作的机制。

有一个规范化规则可以帮助识别这些问题，即字段应仅依赖于表的主键。在存储Speedboat、Cars和Gokart测试结果的汇总表中，与汽车相关的字段不仅取决于测试日期，还取决于车辆ID和车辆类型。测试结果表的主键是测试日期+车辆ID，而车辆类型并不是使测试数据行唯一的因素（即是否有任何方法在01/01/2009 12:30 PM对既是Speedboat又是Car的特定车辆进行测试...不行...无法完成）。

我不能很好地解释规范化规则...但是当我阅读官方描述时，第三/第四/第五正常形式总是让我感到困惑。其中之一（第三/第四/第五）处理依赖于主键且仅依赖于主键的字段。该规则假定已正确识别主键（错误定义主键太容易了）。

- user53794

---

-1 是因为多态关联设计（type 和 id_in_type）不是一种规范化的设计。 - Bill Karwin

嗯...请参考http://en.wikipedia.org/wiki/Fourth_normal_form。比萨饼的例子相当合理。 - user53794

你是在说 {test_id，type} -> -> {id_in_type} 通过了4NF，因此 {test_id，type} 是超键吗？我谈论的是关系的基本定义，其中每个属性表示一个“东西”的值 - 但是id_in_type代表着三种不同的事物。 - Bill Karwin