PostgreSQL多类型媒体条目数据库外键关联设计方案咨询

方案一：可区分外键列 + 检查约束 #

在entries表中为每种媒体类型单独设置外键列，配合枚举列标记类型，再用检查约束确保同一记录仅对应一个有效外键：

-- 定义媒体类型枚举
CREATE TYPE media_type AS ENUM ('game', 'movie', 'tv_show');

-- 创建entries表
CREATE TABLE entries (
    id SERIAL PRIMARY KEY,
    media_type media_type NOT NULL,
    game_id INT REFERENCES games(id),
    movie_id INT REFERENCES movies(id),
    tv_show_id INT REFERENCES tv_shows(id),
    watched_at TIMESTAMP NOT NULL,
    -- 检查约束：保证对应类型的外键非空，其余为空
    CONSTRAINT check_media_fk CHECK (
        (media_type = 'game' AND game_id IS NOT NULL AND movie_id IS NULL AND tv_show_id IS NULL)
        OR (media_type = 'movie' AND movie_id IS NOT NULL AND game_id IS NULL AND tv_show_id IS NULL)
        OR (media_type = 'tv_show' AND tv_show_id IS NOT NULL AND game_id IS NULL AND movie_id IS NULL)
    )
);

这个方案直接利用PostgreSQL的约束机制保证数据完整性，查询时可通过media_type快速定位关联表，无需后端额外做基础校验。

方案二：继承表 + 唯一约束 #

先建一个基础媒体表，让各媒体表继承它，再让entries表关联基础表ID，同时给子表ID加唯一约束避免重复：

-- 基础媒体表
CREATE TABLE media (
    id SERIAL PRIMARY KEY,
    media_type media_type NOT NULL
);

-- 游戏表继承基础表，保留特有属性
CREATE TABLE games (
    platform VARCHAR(50) NOT NULL,
    play_time INT NOT NULL
) INHERITS (media);

-- 电影表继承基础表
CREATE TABLE movies (
    duration INT NOT NULL,
    director VARCHAR(100) NOT NULL
) INHERITS (media);

-- 剧集表继承基础表
CREATE TABLE tv_shows (
    season INT NOT NULL,
    episode_count INT NOT NULL
) INHERITS (media);

-- 给子表ID加唯一约束，避免跨表ID重复
ALTER TABLE games ADD CONSTRAINT games_id_unique UNIQUE (id);
ALTER TABLE movies ADD CONSTRAINT movies_id_unique UNIQUE (id);
ALTER TABLE tv_shows ADD CONSTRAINT tv_shows_id_unique UNIQUE (id);

-- entries表关联基础媒体表
CREATE TABLE entries (
    id SERIAL PRIMARY KEY,
    media_id INT REFERENCES media(id),
    watched_at TIMESTAMP NOT NULL
);

这种方案逻辑上统一了媒体入口，查询所有媒体时可直接查media表，新增媒体类型只需加子表，无需修改entries结构。但要注意PostgreSQL继承表在部分场景（如直接引用子表外键）有局限性，需依赖唯一约束保障关联准确性。

方案三：后端逻辑辅助（无数据库外键约束） #

如果追求表结构极简，可只在entries表存media_type和media_id，由后端逻辑校验对应媒体表中是否存在该ID：

CREATE TABLE entries (
    id SERIAL PRIMARY KEY,
    media_type media_type NOT NULL,
    media_id INT NOT NULL,
    watched_at TIMESTAMP NOT NULL
);

优点是表结构简单，但无法通过数据库约束保证数据完整性，必须在后端业务逻辑中处理校验——比如插入/更新entries时，根据media_type去对应媒体表查询ID是否存在，否则抛出错误。

方案选择建议 #

• 优先保障数据完整性：选方案一，数据库层面直接杜绝无效关联。
• 看重媒体类型扩展性：选方案二，新增类型只需加子表，无需改动entries结构。
• 追求表结构极简且后端逻辑可靠：选方案三，但要做好异常处理。

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