MySQL - 超类型/子类型设计

Question

我需要创建以下数据库：

对于半挂卡车，我不需要额外的子类型，而对于汽车，我需要仅有这三种子类型，并且对于轿车，我需要四种子类型。 对于SELECT语句，我将使用JOIN（规范化的数据库），但我需要找到一种轻松的方法来进行INSERT操作。

车辆表存储常见信息；半挂卡车存储半挂卡车的特定信息；汽车表具有汽车的特定字段和一个与三个子类型相关联的car_type字段；Van、Suv和Sedan（以及其他类型如果需要）应该在一个CAR_TYPE表中。然而，对于Sedan类型，我需要有附加的子类型，这些子类型可能应该包含在另一个表中。 在现实生活中，SUV、货车可以与轿车具有相同的子类型，但在我的情况下不需要。

到目前为止，我的第一个方法是拥有以下表格：

- Vehicle：veh_id、veh_type（Semi, car）、…、other_fields - Vehicle_semis：veh_id、…、other_semis_fields - Vehicle_car：veh_id、car_type（Van、Suv、Sedan）、other_car_specific_fields - Car_type：car_type_id，type - Sedan_type：sedan_type_id，type 我的问题是我不确定这是否是正确的方法，也不知道如何在表之间创建关系。有任何想法吗？

感谢您！

更新：

以下图表基于@Mike的答案：


- Cristian

---

首先要确定此数据库的主要操作（最常用）是什么：读取、写入（插入、更新、删除），因为每种情况的设计都会非常不同。还要考虑有多少数据，将来是否会增长？如果是，增长的幅度是多少？有多少客户端会查询数据库？查询频率有多高？这些只是您需要注意的几个问题。 - Stephan

在您提出的架构中，可能存在读取问题，因为数据非常规范化...想象一下，如果您需要检索一些轿车，您将需要4个或更多的连接，这不是非常高效的，但考虑到低数据量，即使对于许多用户也不应该是问题。我建议使用InnoDB引擎，因为它具有2个关键功能：事务支持和行级锁定，非常适合您的应用程序。 - Stephan

@Stephan - 感谢您关于使用哪个引擎的建议。您说数据过于规范化。您是否有其他设计方式？我想您不会更改Vehicle、Vehicle_Semis和Vehicle_Car这三个表，因为它们具有共同字段和分离字段（或者您会吗）。但是其他表呢？ - Cristian

对于读取部分较为密集的大量数据，通常会采用规范化模式和非规范化模式...在你的情况下，可以使用单个表格来存储所有数据，这样就不需要单独进行联接操作。 - Stephan

@Stephan - 嗯，我想这样做，但我还在困扰如何处理子类型。我希望将子类型以某种方式存储在不同的表中，以便可以从数据库中提取它们并在网站上显示它们。我想使用一个id和一个类型（1，货车; 2，SUV; 3，轿车）。随着数据的增长，如果需要添加其他子类型，我将需要这个。对于轿车子类型也是一样的。 - Cristian

Answer 1

在开始之前，我想指出，“gas”描述的是燃料或一种发动机，而不是一种轿车。在继续这个路径之前，请仔细考虑。（在数据库设计中，语义比大多数人想象的重要。）

你想做的事情相当简单，但并不容易。在这种超类型/子类型设计（也称为独占弧）中，重要的一点是要使有关轿车的行不可能引用有关半卡车等的行。

MySQL使代码更加冗长，因为它不执行CHECK约束。你很幸运；在你的应用程序中，CHECK约束可以被额外的表和外键约束所替代。注释参考上面的SQL。

create table vehicle_types (
  veh_type_code char(1) not null,
  veh_type_name varchar(10) not null,
  primary key (veh_type_code),
  unique (veh_type_name)
);

insert into vehicle_types values
('s', 'Semi-truck'), ('c', 'Car');

在其他平台上，我可能会将这种东西作为CHECK约束实现。当代码的含义对用户来说是显而易见时，您可以这样做。我希望用户知道或能够弄清楚“s”代表半挂车，“c”代表轿车，或者视图/应用程序代码将隐藏代码不让用户看到。

create table vehicles (
  veh_id integer not null,
  veh_type_code char(1) not null,
  other_columns char(1) default 'x',
  primary key (veh_id),
  unique (veh_id, veh_type_code),
  foreign key (veh_type_code) references vehicle_types (veh_type_code)
);

UNIQUE约束条件允许列对{veh_id, veh_type_code}成为外键引用的目标。这意味着一个“汽车”行不能错误地引用一个“半挂车”行。

insert into vehicles (veh_id, veh_type_code) values
(1, 's'), (2, 'c'), (3, 'c'), (4, 'c'), (5, 'c'), 
(6, 'c'), (7, 'c');

create table car_types (
  car_type char(3) not null,
  primary key (car_type)
);

insert into car_types values
('Van'), ('SUV'), ('Sed');

create table veh_type_is_car (
  veh_type_car char(1) not null,
  primary key (veh_type_car)
);

如果在其他平台上，我还会实施作为 CHECK 约束的其他内容（请见下文）。

insert into veh_type_is_car values ('c');

仅有一行。

create table cars (
  veh_id integer not null,
  veh_type_code char(1) not null default 'c',
  car_type char(3) not null,
  other_columns char(1) not null default 'x',
  primary key (veh_id ),
  unique (veh_id, veh_type_code, car_type),
  foreign key (veh_id, veh_type_code) references vehicles (veh_id, veh_type_code),
  foreign key (car_type) references car_types (car_type),
  foreign key (veh_type_code) references veh_type_is_car (veh_type_car)
);

veh_type_code 的默认值，以及对于 veh_type_is_car 的外键引用，保证了此表中的行仅可表示汽车，并且仅能引用汽车。在其他平台上，我会将列 veh_type_code 声明为 veh_type_code char(1) not null default 'c' check (veh_type_code = 'c')。

insert into cars (veh_id, veh_type_code, car_type) values
(2, 'c', 'Van'), (3, 'c', 'SUV'), (4, 'c', 'Sed'),
(5, 'c', 'Sed'), (6, 'c', 'Sed'), (7, 'c', 'Sed');

create table sedan_types (
  sedan_type_code char(1) not null,
  primary key (sedan_type_code)
);

insert into sedan_types values
('g'), ('d'), ('h'), ('e');

create table sedans (
  veh_id integer not null,
  veh_type_code char(1) not null,
  car_type char(3) not null,
  sedan_type char(1) not null,
  other_columns char(1) not null default 'x',
  primary key (veh_id),
  foreign key (sedan_type) references sedan_types (sedan_type_code),
  foreign key (veh_id, veh_type_code, car_type) references cars (veh_id, veh_type_code, car_type)
);

insert into sedans (veh_id, veh_type_code, car_type, sedan_type) values 
(4, 'c', 'Sed', 'g'), (5, 'c', 'Sed', 'd'), (6, 'c', 'Sed', 'h'),
(7, 'c', 'Sed', 'e');

如果你需要构建其他引用轿车的表，例如 gas_sedans、diesel_sedans 等，则需要构建类似于“veh_type_is_car”的单行表，并设置对它们的外键引用。

在生产环境中，我会撤销基本表的权限，并使用：

- 可更新视图来执行插入和更新操作； - 存储过程来执行插入和更新操作。

- Mike Sherrill 'Cat Recall'

---

非常感谢您的回答。这正是我所需要的！有一件事我不太明白，那就是“veh_type_is_car”，它与“cars”表中的“veh_type_code”具有相同的代码“c”。这只是为了更好地编码或查询吗？车辆和汽车之间的关系也包括代码“c”。我为什么要添加那个表呢？ - Cristian

@Cristian：在“汽车”表中有一个外键引用了该表。它确保如果行是关于汽车的，则该列中只能出现“c”。也就是说，它防止将“车辆”表中的半卡车行与“汽车”表中的汽车行关联起来。 - Mike Sherrill 'Cat Recall'

好的，明白了。我想保证只有“c”才能进入“cars”表的角色也可以通过代码来实现。在MySQL和代码中都实现这个功能会不会太过冗余？ - Cristian

取决于您所说的“由代码实现”的含义。如果您可以使用MySQL命令行界面执行除那些声明性约束保证之外的操作，那么不能通过代码实现它们。（数据库管理系统提供的行为和保证与应用程序代码提供的行为和保证不同）。但是，在将这些值发送到数据库管理系统之前，让应用程序代码检查合理值并提供有用的友好建议以修复它们并不罕见。 - Mike Sherrill 'Cat Recall'

非常感谢。我将在应用程序代码中进行许多检查，还会使用您的建议进行数据库检查！ “implemented by code” = 应用程序代码。 - Cristian

Answer 2

我向您推荐以下三个标签下的“信息”选项卡： class-table-inheritance single-table-inheritance shared-primary-key 前两个描述了处理关系数据库中类/子类（又称类型/子类型）情况的两种主要设计模式。第三个描述了使用单个主键的技术，该主键在超类表中分配并传播到子类表。

它们并不能完全回答您提出的问题，但它们可以为整个主题提供一些启示。在SO和DBA领域中，SQL中模拟继承这个话题一次又一次地被提出。

- Walter Mitty