Foreign key for subclasses in SQL

Question

I have a relationship between Pets, Customers, and Shipping Company Invoice and Pets has three subclasses Dog, Cat, and Bird, how should I translate that into SQL, what would the id in Invoice reference? The relationship is a many to many relation.

CREATE TABLE Dog(
    id INT NOT NULL,
    BirthDate Date,
    Size INT,
    Weight INT,
    Price INT NOT NULL,
    Location VARCHAR(30) NOT NULL,
    Disability VARCHAR(50),
    Breed VARCHAR(30)
    CONSTRAINT DOG_PK PRIMARY KEY (id)
)

CREATE TABLE Cat(
    id INT NOT NULL,
    BirthDate Date,
    Size INT,
    Weight INT,
    Price INT NOT NULL,
    Location VARCHAR(30) NOT NULL,
    Disability VARCHAR(50),
    Breed VARCHAR(30)
    CONSTRAINT CAT_PK PRIMARY KEY (id)
)

CREATE TABLE Bird(
    id INT NOT NULL,
    BirthDate Date,
    Size INT,
    Weight INT,
    Price INT NOT NULL,
    Location VARCHAR(30) NOT NULL,
    Disability VARCHAR(50),
    Breed VARCHAR(30),
    Color VARCHAR(30),
    CONSTRAINT BIR_PK PRIMARY KEY (id)
)

CREATE TABLE Invoice(
    C_id INT NOT NULL,
    P_id INT NOT NULL,
    S_id INT NOT NULL,
    CONSTRAINT INV_PK PRIMARY KEY(C_id, P_id, S_id),
    CONSTRAINT INV_CID_FK FOREIGN KEY (C_id) REFERENCES Customers(id),
    CONSTRAINT INV_SID_FK FOREIGN KEY (S_id) REFERENCES Shipping_Company(id),
)

Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) — philipxy, May 14 '23 at 19:50

score 1 · Accepted Answer · answered May 14 '23 at 13:30

You need an Animal entity with a Species (dog, cat, bird, ferret, etc) attribute in place of your Dog, Cat, and Bird entities. SQL data design gets unreasonably complex when you try to use class inheritance.

Put the union of your Dog, Cat, Bird attributes in your Animal entity. You don't have to use every attribute for every animal, that's what NULL is for.

Foreign key for subclasses in SQL

1 Answers1