SQL子类外键设计：宠物子类与发票多对多关系的外键引用方案

一、原始SQL的中文翻译（带注释） #

-- 狗表
CREATE TABLE Dog(
    id INT NOT NULL, -- 主键ID
    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, -- 主键ID
    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, -- 主键ID
    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, -- 客户ID
    P_id INT NOT NULL, -- 宠物ID
    S_id INT NOT NULL, -- 运输公司ID
    CONSTRAINT INV_PK PRIMARY KEY(C_id, P_id, S_id), -- 复合主键
    CONSTRAINT INV_CID_FK FOREIGN KEY (C_id) REFERENCES Customers(id), -- 外键关联客户表ID
    CONSTRAINT INV_SID_FK FOREIGN KEY (S_id) REFERENCES Shipping_Company(id), -- 外键关联运输公司表ID
)

二、核心问题解决：Invoice的P_id该引用哪个表？ #

你当前把Dog、Cat、Bird拆成独立表的设计，会导致P_id无法直接设置外键——三个宠物表的ID是各自独立的，外键只能指向单个表。要适配多对多关联逻辑，必须先统一宠物的建模方式，推荐两种可行方案：

方案1：单表继承（最简单高效） #

建一个统一的Pet表，存放所有宠物的共同属性，用pet_type字段区分狗/猫/鸟，子类特有属性允许为空：

-- 统一宠物表
CREATE TABLE Pet(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -- 宠物主键ID
    pet_type VARCHAR(10) NOT NULL CHECK(pet_type IN ('Dog', 'Cat', 'Bird')), -- 宠物类型
    BirthDate DATE, -- 出生日期
    Size INT, -- 体型
    Weight INT, -- 体重
    Price INT NOT NULL, -- 价格
    Location VARCHAR(30) NOT NULL, -- 所在地
    Disability VARCHAR(50), -- 残疾情况
    Breed VARCHAR(30), -- 品种
    Color VARCHAR(30) -- 鸟类特有：羽毛颜色（狗/猫记录此字段为空即可）
);

这种情况下，Invoice的P_id直接关联Pet表的id即可，完美适配多对多关系。

方案2：类表继承（兼顾规范性和扩展性） #

先建一个Pet基表存储共同属性，再建Dog/Cat/Bird子表存储各自特有属性，子表通过外键关联基表：

-- 宠物基表（存所有宠物的共同属性）
CREATE TABLE Pet(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    BirthDate DATE,
    Size INT,
    Weight INT,
    Price INT NOT NULL,
    Location VARCHAR(30) NOT NULL,
    Disability VARCHAR(50),
    Breed VARCHAR(30)
);

-- 狗表（仅存狗的特有属性，若无特有属性可省略）
CREATE TABLE Dog(
    pet_id INT NOT NULL PRIMARY KEY,
    -- 示例：狗的特有属性，如是否会看门
    FOREIGN KEY (pet_id) REFERENCES Pet(id) -- 关联宠物基表ID
);

-- 猫表（仅存猫的特有属性）
CREATE TABLE Cat(
    pet_id INT NOT NULL PRIMARY KEY,
    -- 示例：猫的特有属性，如掉毛程度
    FOREIGN KEY (pet_id) REFERENCES Pet(id)
);

-- 鸟表（仅存鸟的特有属性）
CREATE TABLE Bird(
    pet_id INT NOT NULL PRIMARY KEY,
    Color VARCHAR(30), -- 羽毛颜色
    FOREIGN KEY (pet_id) REFERENCES Pet(id)
);

这种方式下，Invoice的P_id还是关联Pet表的id，需要查询宠物特有属性时，再通过pet_id关联对应子表。

三、修正后的Invoice表 #

不管用哪种宠物建模方案，修正后的Invoice表都要添加P_id到Pet表的外键约束：

CREATE TABLE Invoice(
    C_id INT NOT NULL, -- 客户ID
    P_id INT NOT NULL, -- 宠物ID
    S_id INT NOT NULL, -- 运输公司ID
    InvoiceDate DATE, -- 开票日期（可添加的发票自身属性）
    TotalAmount DECIMAL(10,2), -- 总金额（可添加的发票自身属性）
    PRIMARY KEY(C_id, P_id, S_id), -- 复合主键，确保同一客户-宠物-运输公司组合唯一
    FOREIGN KEY (C_id) REFERENCES Customers(id),
    FOREIGN KEY (S_id) REFERENCES Shipping_Company(id),
    FOREIGN KEY (P_id) REFERENCES Pet(id) -- 新增：关联统一宠物表
);

四、原始设计的问题 #

你原来把三种宠物拆成独立表的方式，会带来两个明显问题：

1. 无法用一个P_id外键关联所有宠物类型，除非拆成多个发票关联表（比如Invoice_Dog、Invoice_Cat），这会大幅增加查询复杂度和数据冗余。
2. 重复存储大量共同属性（如出生日期、价格等），不符合数据库第三范式，后期维护成本高。

内容的提问来源于stack exchange，提问作者Samil Tajani