MySQL - 超类型/子类型设计

2021-12-26 00:00:00 mysql database-design

我需要创建以下数据库:

I need to create the following database:

对于半卡车，我不需要额外的子类型，而对于汽车，我只需要这 3 个子类型，而对于轿车，我需要四个子类型.对于 SELECT，我将使用 JOIN(规范化数据库)，但我需要找到一种简单的方法来制作 INSERT.

For semi-trucks I don't need extra subtypes, while for Car I need to have only those 3 subtypes and also for Sedan I need the four subtypes. For SELECTs I will use JOINs (normalized database) but I need to find an easy way to make INSERTs.

  • 车辆表存储常用信息
  • Semi-truck 存储半成品的特定信息
  • Car 表具有用于汽车的特定字段和链接到三个子类型的 car_type 字段
  • Van、Suv 和 Sedan(以及其他类型，如果我需要它们)应该在一张桌子 CAR_TYPE
  • 但是，对于 Sedan 类型，我需要有额外的子类型，这些子类型可能应该包含在另一个表中.Suvs 和 Vans 不需要这些子类型(在现实生活中的 suv 中，vans 可以具有与轿车相同的子类型，但在我的情况下不是这样).

我需要完全按照图中的方式创建这个数据库.

I need this database to be created exactly as it is in the diagram.

到目前为止，我的第一种方法是使用以下表格:

So far, my first approach is to have the following tables:

  • 车辆: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，类型
  • Sedan_type:sedan_type_id，类型

我的问题是我不确定这是否是正确的方法，而且我不知道如何在表之间创建关系.

My problem is that I'm not sure this would be the right approach, and I don't know exactly how to create relationships between the tables.

有什么想法吗?

谢谢！

更新:

下图基于@Mike 的回答:

The following diagram is based on @Mike 's answer:

推荐答案

在开始之前，我想指出gas"描述的是燃料或一种发动机，而不是一种轿车.在继续沿着这条路走之前，请三思而后行.(语义在数据库设计中比大多数人想象的更重要.)

Before I get started, I want to point out that "gas" describes either fuel or a kind of engine, not a kind of sedan. Think hard before you keep going down this path. (Semantics are more important in database design than most people think.)

您想做的事情相当简单，但不一定容易.这种超类型/子类型设计(也称为排他弧)的重点是使轿车的行引用半卡车的行等成为不可能.

What you want to do is fairly simple, but not necessarily easy. The important point in this kind of supertype/subtype design (also known as an exclusive arc) is to make it impossible to have rows about sedans referencing rows about semi-trucks, etc..

MySQL 使代码更加冗长，因为它不强制执行 CHECK 约束.你很幸运；在您的应用程序中，CHECK 约束可以替换为附加表和外键约束.注释指的是上面的 SQL.

MySQL makes the code more verbose, because it doesn't enforce CHECK constraints. You're lucky; in your application, the CHECK constraints can be replaced by additional tables and foreign key constraints. Comments refer to the SQL above them.

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"代表汽车，或者视图/应用程序代码会对用户隐藏代码.

This is the kind of thing I might implement as a CHECK constraint on other platforms. You can do that when the meaning of the codes is obvious to users. I'd expect users to know or to figure out that 's' is for semis and 'c' is for cars, or that views/application code would hide the codes from users.

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} 成为外键引用的目标.这意味着汽车"行不可能引用半"行，即使是错误的.

The UNIQUE constraint lets the pair of columns {veh_id, veh_type_code} be the target of a foreign key reference. That means a "car" row can't possibly reference a "semi" row, even by mistake.

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 约束实现的其他东西.(见下文.)

Something else I'd implement as a CHECK constraint on other platforms. (See below.)

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').

The default value for veh_type_code, along with the foreign key reference to veh_type_is_car, guarantees that this rows in this table can be only about cars, and can only reference vehicles that are cars. On other platforms, I'd just declare the column veh_type_code as 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');

如果你需要构建额外的引用sedans的表，比如gas_sedans、diesel_sedans等，那么你需要构建类似于veh_type_is_car"的单行表，并为其设置外键引用.

If you have to build additional tables that reference sedans, such as gas_sedans, diesel_sedans, etc., then you need to build one-row tables similar to "veh_type_is_car" and set foreign key references to them.

在生产中，我会撤销对基表的权限，或者使用

In production, I'd revoke permissions on the base tables, and either use

  • 用于插入和更新的可更新视图，或
  • 用于执行插入和更新的存储过程.

相关文章