MySQL - 超类型/子类型设计 [英] MySQL - Supertype/Subtype design

问题描述

我需要创建以下数据库：

对于半卡车，我不需要额外的子类型，而对于汽车，我需要只有这3种子类型，也适用于轿车我需要四种亚型。
对于SELECT，我将使用JOIN（标准化数据库），但是我需要找到一个简单的方法来进行INSERT。

• 车辆表存储公共信息


• 半卡车存储半成品的特定信息


• 车载表具有汽车的特定字段和与三个相关联的car_type字段子类型


• Van，Suv和Sedan（和其他类型，如果我需要它们）应该在一个表中CAR_TYPE


• 然而，对于Sedan类型I需要额外的子类型，可能应该包含在另一个表中。这些亚型不需要Suvs和Vans（在现实生活中，货车可以有与轿车相同的亚型，但不是在我的情况下）。

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

• 车辆：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，键入

我的问题是，我不知道这将是正确的方法，我不知道如何创建表之间的关系。

任何想法？

谢谢！

更新：

下图是基于@Mike的答案：

解决方案

在开始之前，我想指出，燃气描述了燃油或一种发动机，而不是一种轿车。在你继续走下去之前，先思考一下。 （语义在数据库设计中比大多数人认为的更重要。）

你想做的是相当简单但不一定容易。这种超类型/子类型设计（也称为排弧）的重点在于使得不可能有关于关于半卡车等的行的引擎的行。

MySQL使代码更详细，因为它不执行CHECK约束。你很幸运在您的应用程序中，CHECK约束可以被其他表和外键限制所替代。评论参考上面的SQL 。

 创建表车辆类型（
 veh_type_code char 1）not null，
 veh_type_name varchar（10）not null，
主键（veh_type_code），
 unique（veh_type_name）
）; 
 
插入车辆类型值
（'s'，'半卡车'），（'c'，'Car'）; 
  

这是我在其他平台上作为CHECK约束实现的一种事情。当代码的含义对用户来说是显而易见的，你可以这样做。我希望用户知道或者想出来的是半决赛，c是用于汽车，或者视图/应用程序代码将隐藏用户的代码。

pre> 创建表格车辆（
veh_id integer not null，
veh_type_code char（1）not null，
other_columns char（1）default'x '，
主键（veh_id），
unique（veh_id，veh_type_code），
外键（veh_type_code）引用车辆类型（veh_type_code）
）;


UNIQUE约束使一对列（veh_id，veh_type_code）成为外键引用的对象。这意味着汽车行不可能引用半行，即使是错误的。

 插入车辆veh_id，veh_type_code）value 
（1，'s'），（2，'c'），（3，'c'），（4，'c'），（5，'c'），
（6，'c'），（7，'c'）; 
 
创建表car_types（
 car_type char（3）not null，
主键（car_type）
）; 
 
插入car_types值
（'Van'），（'SUV'），（'Sed'）; 
 
创建表veh_type_is_car（
 veh_type_car char（1）not null，
主键（veh_type_car）
）; 
  

其他我在其他平台上作为一个CHECK约束实现。 （见下文）

  insert into veh_type_is_car values（'c'）; 
  

只有一行。

创建表车（
 veh_id integer not null，
 veh_type_code char（1）not null默认'c'，
 car_type char（3）not null，
 other_columns char（1）not null默认'x'，
主键（veh_id），
 unique（veh_id，veh_type_code，car_type），
外键（veh_id，veh_type_code）车辆（veh_id，veh_type_code），
外键（car_type）引用car_types（car_type），
外键（veh_type_code）引用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'）。

 插入汽车（veh_id，veh_type_code，car_type）值
（2，'c'，'Van'）， 'c'，'SUV'），（4，'c'，'Sed'），
（5，'c'，'Sed'），（6，'c' 7，'c'，'Sed'）; 
 
创建表sedan_types（
 sedan_type_code char（1）not null，
主键（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默认'x'，
主键（veh_id），
外键（sedan_type）引用sedan_types（sedan_type_code），
外键（veh_id，veh_type_code，car_type）引用汽车（veh_id，veh_type_code，car_type）
）; 
 
插入轿车（veh_id，veh_type_code，car_type，sedan_type）值
（4，'c'，'Sed'，'g'），（5，'c' '，'d'），（6，'c'，'Sed'，'h'），
（7，'c'，'Sed'，'e'）; 
  

如果您必须构建引用轿车的附加表，例如gas_sedans，diesel_sedans等，您需要构建类似veh_type_is_car的一行表，并设置外键引用。

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

• 可更新视图来进行插入和更新，或者


• 存储过程来做插入和更新。

I need to create the following database:

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.

• Vehicle table stores common information
• Semi-truck stores specific information for semis
• Car tables has specific fields for cars and a car_type field which is linked to the three subtypes
• Van, Suv and Sedan (and other types if I would need them) should be in one table CAR_TYPE
• However, for Sedan type I need to have additional subtypes which maybe should be contained in another table. These subtypes are not needed for Suvs and Vans (in real life suv, vans can have the same subtypes as sedans but not in my case).

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:

• 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

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.

Any ideas?

Thank you!

UPDATE:

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

解决方案

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

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)
);

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)
);

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

insert into veh_type_is_car values ('c');

Only one row ever.

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)
);

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

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

• updatable views to do the inserts and updates, or
• stored procedures to do the inserts and updates.

这篇关于MySQL - 超类型/子类型设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！