如何为复杂嵌套结构设计SQL表并实现正确关联查询

现有设计的问题 #

• 表拆分逻辑混乱：跨schema存储关联数据且没有外键约束，很容易产生孤立的无效数据；带嵌套的一级配置和普通一级配置拆分存储，查询逻辑无法统一。
• 关联关系缺失：delivery_intervals表中存储的关联ID没有和区间配置表的主键做绑定，实际查询时无法把morning/evening这类二级区间正确挂载到对应的一级配置下。
• 字段语义错位：delivery_intervals的val字段存储的是关联标识而非业务值，和业务定义的字段含义不符，后续维护极易出现逻辑错误。

合理表设计思路 #

完全对齐业务定义的两层结构体，仅需2张表即可实现，通过明确外键保证数据一致性，统一一级配置存储入口：

一级配置表 delivery_conditions #

存储所有第一层结构数据，普通键值对直接存值，带嵌套区间的配置值留空，通过关联表取子项：

CREATE TABLE delivery_conditions (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    title VARCHAR(255) NOT NULL UNIQUE,
    val VARCHAR(255) DEFAULT NULL
);

字段说明：

• id：主键
• title：一级配置标题，如self-delivery reception、order acceptance
• val：普通配置的具体值，带嵌套的配置该字段为NULL

二级区间配置表 delivery_condition_intervals #

存储所有第二层Intervals结构体数据，通过外键关联一级配置，不需要拆分到独立schema：

CREATE TABLE delivery_condition_intervals (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    condition_id BIGINT NOT NULL 
        REFERENCES delivery_conditions(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    val VARCHAR(255) NOT NULL,
    UNIQUE(condition_id, title)
);

字段说明：

• id：主键
• condition_id：关联的一级配置主键，级联删除保证删除一级配置时自动清理对应二级区间
• title：二级区间标题，如morning、evening
• val：区间值，如12:00-13:00
• 唯一约束保证同一个一级配置下不会出现重名的二级区间

数据写入示例 #

对应给出的前端数据样例，写入逻辑清晰无歧义：

-- 写入全部一级配置
INSERT INTO delivery_conditions(title, val)
VALUES 
('self-delivery reception', '10:00-13:00'),
('deadline for admission', '19:00'),
('deadline', '12:00'),
('order acceptance', NULL);

-- 写入order acceptance关联的二级区间，假设order acceptance对应的主键ID为4
INSERT INTO delivery_condition_intervals(condition_id, title, val)
VALUES
(4, 'morning', '12:00-13:00'),
(4, 'evening', '16:00-17:00');

查询实现 #

通过左关联+JSON聚合函数一次性返回全量数据，结构完全匹配业务定义的结构体：

SELECT 
    dc.title,
    dc.val,
    COALESCE(
        json_agg(
            json_build_object(
                'title', dci.title,
                'value', dci.val
            ) FILTER (WHERE dci.id IS NOT NULL)
        ),
        '[]'::JSON
    ) AS intervals
FROM delivery_conditions dc
LEFT JOIN delivery_condition_intervals dci ON dc.id = dci.condition_id
GROUP BY dc.id, dc.title, dc.val;

返回结果规则：

• 无嵌套的普通配置（如self-delivery reception）：val字段返回具体时间值，intervals返回空数组
• 带嵌套的配置（如order acceptance）：val字段为NULL，intervals返回组装好的二级区间对象数组

设计优势 #

• 模型完全对齐业务结构体，无冗余表，外键约束从数据库层避免脏数据产生
• 所有一级配置统一入口查询，不需要多表合并结果
• 扩展性强，后续二级结构体新增字段只需在二级表加字段即可，不需要调整整体架构
• 查询灵活，支持直接通过关联表筛选特定区间的配置，不需要解析JSON字段

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