像数据库设计中的继承 [英] Something like inheritance in database design
问题描述
您可以创建三个单独的表：SpeedboatTests，CarTests和GokartTests。但是，您的各个列中的很多列将在每个表中都是相同的（例如，执行测试的人员的员工ID，碰撞方向（前端，后端）等）。然而，很多列将会有所不同，所以您不希望将所有测试数据放在一个表中，因为您将有相当多的列，对于快艇而言，这些列将始终为空，而这些列将始终为null对于汽车而言，为零，而对于卡丁车来说，相当多的将永远是空的。假设您还想存储一些与测试无直接关系的信息（例如被测试的设备的员工ID）。这些列似乎没有什么可以放在测试表中，特别是因为它们将在同一辆车上的所有测试重复。
让我说明一个可能的表格安排，所以你可以看到涉及的问题。
Speedboats
id | col_about_speedboats_but_not_tests1 | col_about_speedboats_but_not_tests2
汽车
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
测试
id |类型| id_in_type | col_about_all_tests1 | col_about_all_tests2
（id_in_type将引用下列三个表之一的id列，
取决于类型的值）
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
这个结构有什么好坏，什么是首选的实现方式？
如果还有一些信息适用于您喜欢在车辆表格中的所有车辆？ CarTests表会看起来像...
id | vehicle_id | ...
带有这样的车辆表：
id |类型| id_in_type
（id_in_type指向快艇，汽车或卡丁车的id）
这只是得到成为一个皇家混乱似乎。
类型和 id_in_type 设计称为多态关联。这种设计以多种方式打破了规范化的规则。如果没有其他的，它应该是一个红旗，你不能声明一个真正的外键约束，因为 id_in_type 可以引用几个
这是一个更好的方法来定义你的表：
- 抽象表
车辆为所有车辆子类型和车辆测试提供抽象参考点。 - 每个车辆子类型具有主键不自动递增，而是引用
车辆。 - 每个测试子类型都有一个主键，自动递增，而是引用
测试。 - 每个测试子类型也有一个对应的车辆子类型的外键
以下是示例DDL：
CREATE TABLE车辆（
vehicle_id INT AUTO_INCREMENT PRIMARY KEY
）;
CREATE TABLE快艇（
vehicle_id INT PRIMARY KEY，
col_about_speedboats_but_not_tests1 INT，
col_about_speedboats_but_not_tests2 INT，
FOREIGN KEY（vehicle_id）参考车辆（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）参考车辆（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）参考车辆（vehicle_id）
）;
CREATE TABLE测试（
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）参考测试（test_id），
FOREIGN KEY（vehicle_id）参考快艇（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）参考测试（test_id），
FOREIGN KEY（vehicle_id）参考汽车（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）参考测试（test_id），
FOREIGN KEY（vehicle_id）REFERENCES Gokarts（vehicle_id）
）;
您可以选择申报 Tests.vehicle_id 引用 Vehicles.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
Vehiclesto 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屋！
