类似于数据库设计中的继承 [英] Something like inheritance in database design

问题描述

假设您正在建立一个数据库来存储各种车辆的碰撞测试数据.您想存储快艇、汽车和卡丁车的碰撞测试数据.

您可以创建三个单独的表:SpeedboatTests、CarTests 和 GokartTests.但是每个表中的很多列都将相同(例如，执行测试的人员的员工 ID、碰撞方向(前、侧、后)等).但是，很多列会有所不同，因此您不想将所有测试数据放在一个表中，因为您将有很多列对于快艇来说始终为空，很多列始终为空对于汽车来说为 null，对于卡丁车来说，很多总是为 null.

假设您还想存储一些与测试没有直接关系的信息(例如被测试事物的设计者的员工 ID).这些列似乎根本不适合放在测试"表中，尤其是因为它们会在同一辆车上的所有测试中重复.

让我举例说明一种可能的表格排列方式，以便您了解所涉及的问题.

<前>快艇身份证 |col_about_speedboats_but_not_tests1 |col_about_speedboats_but_not_tests2汽车身份证 |col_about_cars_but_not_tests1 |col_about_cars_but_not_tests2卡丁车身份证 |col_about_gokarts_but_not_tests1 |col_about_gokarts_but_not_tests2测试身份证 |类型 |id_in_type |col_about_all_tests1 |col_about_all_tests2(id_in_type 将引用接下来三个表之一的 id 列，取决于类型的值)快艇测试身份证 |speedboat_id |col_about_speedboat_tests1 |col_about_speedboat_tests2汽车测试身份证 |car_id |col_about_car_tests1 |col_about_car_tests2卡丁车测试身份证 |gokart_id |col_about_gokart_tests1 |col_about_gokart_tests2

这种结构的优点/缺点是什么?实现这种结构的首选方式是什么?

如果还有一些适用于您希望在 Vehicles 表中包含的所有车辆的信息怎么办?CarTests 表会看起来像...

<前>身份证 |车辆_id |...使用像这样的 Vehicles 表:身份证 |类型 |id_in_type(id_in_type 指向快艇、汽车或卡丁车的 ID)

这似乎是一团乱麻.应该如何设置这样的东西?

解决方案

type 和 id_in_type 设计被称为 多态关联.这种设计以多种方式打破了规范化规则.如果不出意外，您不能声明真正的外键约束应该是一个危险信号，因为id_in_type 可能会引用多个表中的任何一个.

这是定义表格的更好方法:

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

这是示例 DDL:

CREATE TABLE Vehicles (Vehicle_id INT AUTO_INCREMENT PRIMARY KEY);创建表快艇(Vehicle_id INT PRIMARY KEY,col_about_speedboats_but_not_tests1 INT，col_about_speedboats_but_not_tests2 INT，外键(vehicle_id)参考车辆(vehicle_id));创建桌车(Vehicle_id INT PRIMARY KEY,col_about_cars_but_not_tests1 INT，col_about_cars_but_not_tests2 INT，外键(vehicle_id)参考车辆(vehicle_id));创建桌卡丁车(Vehicle_id INT PRIMARY KEY,col_about_gokarts_but_not_tests1 INT，col_about_gokarts_but_not_tests2 INT，外键(vehicle_id)参考车辆(vehicle_id));创建表测试(test_id INT AUTO_INCREMENT PRIMARY KEY，col_about_all_tests1 INT，col_about_all_tests2 INT);创建表快艇测试(test_id INT PRIMARY KEY，Vehicle_id INT 非空，col_about_speedboat_tests1 INT，col_about_speedboat_tests2 INT，外键(test_id)参考测试(test_id)，外键(vehicle_id)参考快艇(vehicle_id));创建表 CarTests (test_id INT PRIMARY KEY，Vehicle_id INT 非空，col_about_car_tests1 INT，col_about_car_tests2 INT，外键(test_id)参考测试(test_id)，外键(vehicle_id)参考汽车(vehicle_id));创建表 GokartTests (test_id INT PRIMARY KEY，Vehicle_id INT 非空，col_about_gokart_tests1 INT，col_about_gokart_tests2 INT，外键(test_id)参考测试(test_id)，外键(vehicle_id)参考 Gokarts(vehicle_id));

您也可以声明引用 Vehicles.vehicle_id 的 Tests.vehicle_id 并去掉每个测试子类型表中的 Vehicle_id 外键，但这会导致异常，例如引用 gokart id 的快艇测试.

Suppose you were setting up a database to store crash test data of various vehicles. You want to store data of crash tests for speedboats, cars, and go-karts.

You could create three separate tables: SpeedboatTests, CarTests, and GokartTests. But a lot of your columns are going to be the same in each table (for example, the employee id of the person who performed the test, the direction of the collision (front, side, rear), etc.). However, plenty of columns will be different, so you don't want to just put all of the test data in a single table because you'll have quite a few columns that will always be null for speedboats, quite a few that will always be null for cars, and quite a few that will always be null for go-karts.

Let's say you also want to store some information that isn't directly related to the tests (such as the employee id of the designer of the thing being tested). These columns don't seem right to put in a "Tests" table at all, especially because they'll be repeated for all tests on the same vehicle.

Let me illustrate one possible arrangement of tables, so you can see the questions involved.

Speedboats
id | col_about_speedboats_but_not_tests1 | col_about_speedboats_but_not_tests2

Cars
id | col_about_cars_but_not_tests1 | col_about_cars_but_not_tests2

Gokarts
id | col_about_gokarts_but_not_tests1 | col_about_gokarts_but_not_tests2

Tests
id | type | id_in_type | col_about_all_tests1 | col_about_all_tests2
(id_in_type will refer to the id column of one of the next three tables,
depending on the value of type)

SpeedboatTests
id | speedboat_id | col_about_speedboat_tests1 | col_about_speedboat_tests2

CarTests
id | car_id | col_about_car_tests1 | col_about_car_tests2

GokartTests
id | gokart_id | col_about_gokart_tests1 | col_about_gokart_tests2

What is good/bad about this structure and what would be the preferred way of implementing something like this?

What if there's also some information that applies to all vehicles that you'd prefer to have in a Vehicles table? Would the CarTests table then look something like...

id | vehicle_id | ...

With a Vehicles table like this:
id | type | id_in_type
(with id_in_type pointing to the id of either a speedboat, car, or go-kart)

This is just getting to be a royal mess it seems. How SHOULD something like this be set up?

解决方案

The type and id_in_type design is called Polymorphic Associations. This design breaks rules of normalization in multiple ways. If nothing else, it should be a red flag that you can't declare a real foreign key constraint, because the id_in_type may reference any of several tables.

Here's a better way of defining your tables:

• Make an abstract table Vehicles to provide an abstract reference point for all vehicle sub-types and vehicle tests.
• Each vehicle sub-type has a primary key that does not auto-increment, but instead references Vehicles.
• Each test sub-type has a primary key that does not auto-increment, but instead references Tests.
• Each test sub-type also has a foreign key to the corresponding vehicle sub-type.

Here's sample 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)
);

You could alternatively declare Tests.vehicle_id which references Vehicles.vehicle_id and get rid of the vehicle_id foreign keys in each test sub-type table, but that would permit anomalies, such as a speedboat test that references a gokart's id.

这篇关于类似于数据库设计中的继承的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！