1

I am modeling some things with an "is a" relationship (aka inheritance), and there were two ways to go about it in regards to the inherited table's primary key and foreign key pointing back to the parent table:

  • Strategy #1 was to always create a new surrogate key on each inherited table, and then store the reference to the parent table's primary key in a separate column (and have unique constraint on that column to ensure a 1 to 1 mapping).

  • Strategy #2 was to make the primary key value of the inherited table have the same value as the parent table's primary key.

I think there is a clear winner here (Strategy #2), but I want to make sure there isn't some potential issue I am missing.

Here's the code (targeting SQL Server, but maybe generic) elaborating each strategy:

------Strategy #1: always create a surrogate key for a child table (separate column holds parent table's primary key)----------
drop table if exists TruckAsset
drop table if exists VehicleAsset
drop table if exists Asset

create table Asset
(
    AssetId int not null,
    primary key (AssetId)
)

create table VehicleAsset
(
    VehicleAssetId int not null, /*surrogate key*/
    AssetId int not null,   
    VehicleRegistrationExpirationDate date not null, /*specialization of Asset*/

    primary key (VehicleAssetId),
    constraint UQ_AssetId unique (AssetId),
    foreign key (AssetId) references Asset(AssetId)
)

create table TruckAsset
(
    TruckAssetId int not null, /*surrogate key*/
    VehicleAssetId int not null,
    CargoCapacityCubicMeters float not null, /*specialization of VehicleAsset*/

    primary key (TruckAssetId),
    constraint UQ_VehicleAssetId unique (VehicleAssetId),
    foreign key (VehicleAssetId) references VehicleAsset(VehicleAssetId)
)

INSERT INTO Asset(AssetId)VALUES(1234)
INSERT INTO VehicleAsset(VehicleAssetId, AssetId, VehicleRegistrationExpirationDate) VALUES (5678, 1234, '2020-01-01')
INSERT INTO TruckAsset(TruckAssetId, VehicleAssetId, CargoCapacityCubicMeters) VALUES (9999, 5678, 8.80977)

GO

versus this

------Strategy #2: primary key references parent table's primary key directly----------
drop table if exists TruckAsset
drop table if exists VehicleAsset
drop table if exists Asset

create table Asset
(
    AssetId int not null,
    primary key (AssetId)
)

create table VehicleAsset
(
    VehicleAssetId int not null,
    VehicleRegistrationExpirationDate date not null, /*specialization of Asset*/

    primary key (VehicleAssetId),
    foreign key (VehicleAssetId) references Asset(AssetId)
)

create table TruckAsset
(
    TruckAssetId int not null,
    CargoCapacityCubicMeters float not null, /*specialization of VehicleAsset*/

    primary key (TruckAssetId),
    foreign key (TruckAssetId) references VehicleAsset(VehicleAssetId)
)

INSERT INTO Asset(AssetId)VALUES(1234)
INSERT INTO VehicleAsset(VehicleAssetId, VehicleRegistrationExpirationDate) VALUES (1234, '2020-01-01')
INSERT INTO TruckAsset(TruckAssetId, CargoCapacityCubicMeters) VALUES (1234, 8.80977)
----------------------------------------
1

Strategy #2 has a name. It is called Shared Primary Key. It it widely used with is a type relationships. There is a tag over in Stackoverflow that groups question related to this concept.

See link

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.