Here is an interesting take on creating supertypes/subtypes in MySQL...
https://stackoverflow.com/questions/17405702/mysql-supertype-subtype-design
Is it really necessary to use composite keys?
About halfway down the page, someone proposes this design...
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)
);
He says...
And then...
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)
);
Comments/Questions:
Why would the author combine veh_id and veh_type_code in a unique index and use it as a foreign key?
By definition, a supertype should be generic and apply to all subtypes, so there should be no reference to cars or semis in the vehicle supertype.
Other examples I have seen online just use the same key between supertype and subtype.
This entire topic is still very confusing to me...