MySQL – Supertipo / Subtipo de diseño

Necesito crear la siguiente base de datos:

enter image description here

Para las semirremolques no necesito subtipos adicionales, mientras que para el auto necesito tener solo esos 3 subtipos y también para Sedán necesito los cuatro subtipos. Para SELECT usaré JOIN (base de datos normalizada) pero necesito encontrar una manera fácil de hacer INSERT.

  • La mesa del vehículo almacena información común
  • Semi-camión almacena información específica para semis
  • Las tablas de autos tienen campos específicos para automóviles y un campo de tipo_car, que está vinculado a los tres subtipos
  • Van, Suv y Sedan (y otros tipos si los necesitaría) deberían estar en una tabla CAR_TYPE
  • Sin embargo, para el tipo Sedan, necesito tener subtipos adicionales que tal vez deberían estar en otra tabla. Estos subtipos no son necesarios para Suvs y Vans (en el vehículo deportivo utilitario de la vida real, las camionetas pueden tener los mismos subtipos que los sedanes pero no en mi caso).

Necesito que esta base de datos se cree exactamente como está en el diagtwig.

Hasta ahora, mi primer enfoque es tener las siguientes tablas:

  • Vehículo: veh_id, veh_type (Semi, coche), …, 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

Mi problema es que no estoy seguro de que este sea el enfoque correcto, y no sé exactamente cómo crear relaciones entre las tablas.

¿Algunas ideas?

¡Gracias!

ACTUALIZAR:

El siguiente diagtwig se basa en la respuesta de @Mike: enter image description here

Antes de comenzar, quiero señalar que “gas” describe ya sea combustible o un tipo de motor, no un tipo de sedán. Piensa bien antes de seguir por este camino. (La semántica es más importante en el diseño de la base de datos de lo que la mayoría de las personas piensa).

Lo que quiere hacer es bastante simple, pero no necesariamente fácil. El punto importante en este tipo de diseño de supertipo / subtipo (también conocido como arco exclusivo) es hacer que sea imposible tener filas sobre sedanes que hagan referencia a filas sobre semirremolques, etc.

MySQL hace que el código sea más detallado, porque no impone restricciones CHECK. Tienes suerte; en su aplicación, las restricciones CHECK pueden ser reemplazadas por tablas adicionales y restricciones de clave externa. Los comentarios se refieren al SQL sobre ellos.

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

Este es el tipo de cosas que podría implementar como una restricción CHECK en otras plataformas. Puedes hacer eso cuando el significado de los códigos es obvio para los usuarios. Esperaría que los usuarios supieran o descubrieran que ‘s’ es para semis y que ‘c’ es para automóviles, o que las vistas / códigos de aplicaciones ocultarían los códigos a los usuarios.

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

La restricción ÚNICA permite que el par de columnas {veh_id, veh_type_code} sea el objective de una referencia de clave externa. Eso significa que una fila de “automóvil” no puede hacer referencia a una fila “semi”, incluso por error.

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

Algo más que implementaría como una restricción CHECK en otras plataformas. (Vea abajo.)

 insert into veh_type_is_car values ('c'); 

Solo una fila.

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

El valor predeterminado para veh_type_code, junto con la referencia de clave externa a veh_type_is_car, garantiza que estas filas en esta tabla solo pueden referirse a automóviles, y solo pueden hacer referencia a vehículos que son autos. En otras plataformas, solo declararía la columna veh_type_code como 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'); 

Si tiene que crear tablas adicionales que hagan referencia a sedanes, como gas_sedans, diesel_sedans, etc., entonces necesita construir tablas de una fila similares a “veh_type_is_car” y establecer referencias de claves externas a ellas.

En producción, revocaría permisos en las tablas base, y usaría

  • vistas actualizables para hacer las inserciones y actualizaciones, o
  • procedimientos almacenados para hacer las inserciones y actualizaciones.

Lo remito a la pestaña “Información” debajo de las siguientes tres tags:

class-table-inheritance herencia de tabla única clave-primaria compartida

Los primeros dos describen los dos principales patrones de diseño para tratar una situación de clase / subclase (también conocido como tipo / subtipo) cuando se diseña una base de datos relacional. El tercero describe una técnica para usar una sola clave primaria que se asigna en la tabla de superclase y se propaga a las tablas de subclase.

No responden completamente las preguntas que plantea, pero arrojan algo de luz sobre el tema completo. Este tema, de imitar la herencia en SQL, aparece una y otra vez tanto en SO como en el área de DBA.