设计可引用多表的PostgreSQL 10数据库表方案咨询

根据你描述的场景——两个有共同属性但又各有差异的表，且Trailers明确依附于MotorVehicles——我整理了三种实用的设计方案，每种都有自己的适用场景，你可以按需选择：

方案一：PostgreSQL原生表继承（推荐，贴合PostgreSQL特性） #

PostgreSQL支持表继承，非常适合这种“通用属性+专属属性”的场景。我们可以先建一个存放共同属性的父表，再让两个子表继承它并扩展专属字段：

1. 创建父表（存放通用属性） #

CREATE TABLE RoadVehicle (
    Id SERIAL PRIMARY KEY,
    PaintColor VARCHAR(50) NOT NULL,
    Capacity INTEGER
);

这个父表可以看作所有道路工具的“抽象基类”，不用往里面插入实际数据，只提供字段模板。

2. 创建MotorVehicles子表 #

继承父表的所有字段，同时添加专属的EngineSize：

CREATE TABLE MotorVehicles (
    EngineSize INTEGER NOT NULL
) INHERITS (RoadVehicle);

3. 创建Trailers子表 #

同样继承父表，添加专属的外键字段AttachedVehicle（关联MotorVehicles的Id）：

CREATE TABLE Trailers (
    AttachedVehicle INTEGER NOT NULL REFERENCES MotorVehicles(Id)
) INHERITS (RoadVehicle);

优势：

• 统一查询：直接查父表SELECT * FROM RoadVehicle;就能获取所有MotorVehicles和Trailers的记录，无需JOIN
• 全局唯一Id：父表的SERIAL序列会被子表共享，确保所有道路工具的Id不重复，方便后续做统一引用
• 结构清晰：每个子表只维护自己的专属字段，避免冗余

注意事项：

• 父表的索引不会自动同步到子表，需要手动在子表上创建专属索引（比如给Trailers的AttachedVehicle加索引）
• 如果需要给子表单独添加约束（比如MotorVehicles的EngineSize范围限制），直接在子表上定义即可

方案二：共享主键的规范化设计（传统关系型数据库风格） #

如果你更倾向于经典的规范化设计，不想用PostgreSQL的继承特性，可以用“父表存通用属性，子表存专属属性，通过主键一对一关联”的模式：

1. 创建父表RoadVehicle #

新增VehicleType字段用来区分类型，确保数据一致性：

CREATE TABLE RoadVehicle (
    Id SERIAL PRIMARY KEY,
    VehicleType VARCHAR(20) NOT NULL CHECK (VehicleType IN ('MotorVehicle', 'Trailer')),
    PaintColor VARCHAR(50) NOT NULL,
    Capacity INTEGER
);

2. 创建MotorVehicles子表 #

主键直接引用RoadVehicle的Id，确保一对一关联：

CREATE TABLE MotorVehicles (
    Id INTEGER PRIMARY KEY REFERENCES RoadVehicle(Id),
    EngineSize INTEGER NOT NULL
);

3. 创建Trailers子表 #

同样用主键关联父表，同时保留外键关联MotorVehicles：

CREATE TABLE Trailers (
    Id INTEGER PRIMARY KEY REFERENCES RoadVehicle(Id),
    AttachedVehicle INTEGER NOT NULL REFERENCES MotorVehicles(Id)
);

优势：

• 符合传统规范化设计理念，团队接受度高
• 约束管理更直观，比如可以通过触发器确保MotorVehicles和Trailers的记录与父表的VehicleType匹配
• 扩展性强，后续新增其他道路工具类型时，只需要新增子表即可

注意事项：

• 查询需要用JOIN，比如查询所有带EngineSize的MotorVehicles：

  SELECT rv.*, mv.EngineSize 
  FROM RoadVehicle rv 
  JOIN MotorVehicles mv ON rv.Id = mv.Id 
  WHERE rv.VehicleType = 'MotorVehicle';
  

方案三：单表继承（极简模式，适合字段差异小的场景） #

如果两个表的字段差异不大，且你希望尽量简化表结构，可以把所有字段放到一个表中，用类型字段区分，通过CHECK约束确保数据合法性：

CREATE TABLE RoadVehicle (
    Id SERIAL PRIMARY KEY,
    VehicleType VARCHAR(20) NOT NULL CHECK (VehicleType IN ('MotorVehicle', 'Trailer')),
    PaintColor VARCHAR(50) NOT NULL,
    Capacity INTEGER,
    -- 专属字段
    EngineSize INTEGER,
    AttachedVehicle INTEGER REFERENCES RoadVehicle(Id),
    -- 核心约束：确保类型对应字段非空/为空
    CHECK (
        (VehicleType = 'MotorVehicle' AND EngineSize IS NOT NULL AND AttachedVehicle IS NULL)
        OR
        (VehicleType = 'Trailer' AND AttachedVehicle IS NOT NULL AND EngineSize IS NULL)
    )
);

优势：

• 表结构最简单，查询无需JOIN，性能开销小
• 维护成本低，不需要管理多个表的关联关系

注意事项：

• 后续如果新增更多道路工具类型，表字段会越来越多，容易变得臃肿
• 空字段可能会让数据看起来不够直观，需要依赖VehicleType字段来理解记录含义

总结建议 #

• 如果你的团队熟悉PostgreSQL特性，且经常需要统一查询所有道路工具，优先选方案一
• 如果更偏向传统关系型数据库设计，或者需要严格的约束管理，选方案二
• 如果字段差异极小，查询需求简单，选方案三

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