如何构建带层级结构的PostgreSQL多对多关系数据库？

针对你需要支持可嵌套组件+多套独立排序结构的需求，推荐采用「组件基础表 + 独立层级配置表」的设计方案，具体如下：

1. 基础组件表（已存在的components） #

确保components表仅存储组件本身的属性，不耦合层级或排序信息，示例结构：

CREATE TABLE components (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    -- 其他组件专属字段（如类型、描述等）
);

2. 层级配置表（核心设计） #

新建component_hierarchies表，用来存储不同排序方案下的组件嵌套关系和排序顺序，每套方案的层级结构完全独立：

CREATE TABLE component_hierarchies (
    id SERIAL PRIMARY KEY,
    hierarchy_scheme VARCHAR(255) NOT NULL, -- 标识不同排序方案，如"默认排序"、"自定义方案A"
    parent_component_id INT REFERENCES components(id), -- 父组件ID，顶级组件设为NULL
    child_component_id INT REFERENCES components(id) NOT NULL, -- 子组件ID
    sort_order INT NOT NULL, -- 同层级内的排序优先级（数值越小越靠前）
    -- 唯一约束避免重复数据
    UNIQUE(hierarchy_scheme, parent_component_id, child_component_id),
    UNIQUE(hierarchy_scheme, parent_component_id, sort_order)
);

设计逻辑说明 #

• 用hierarchy_scheme区分不同排序方案，每套方案的父子关系、排序互不干扰
• 顶级组件通过parent_component_id = NULL标识
• sort_order精准控制同一父级下子组件的展示顺序

3. 数据插入示例 #

以你给出的两套排序方案为例：

方案1：默认排序 #

-- 插入顶级组件
INSERT INTO component_hierarchies (hierarchy_scheme, parent_component_id, child_component_id, sort_order)
VALUES 
('默认排序', NULL, 1, 1),
('默认排序', NULL, 4, 2);

-- 插入组件1的子节点
INSERT INTO component_hierarchies (hierarchy_scheme, parent_component_id, child_component_id, sort_order)
VALUES 
('默认排序', 1, 2, 1),
('默认排序', 1, 3, 2);

-- 插入组件4的子节点及嵌套层级
INSERT INTO component_hierarchies (hierarchy_scheme, parent_component_id, child_component_id, sort_order)
VALUES 
('默认排序', 4, 5, 1),
('默认排序', 4, 6, 2),
('默认排序', 6, 7, 1),
('默认排序', 6, 8, 2);

方案2：自定义排序 #

INSERT INTO component_hierarchies (hierarchy_scheme, parent_component_id, child_component_id, sort_order)
VALUES 
('自定义排序', NULL, 3, 1),
('自定义排序', NULL, 5, 2),
('自定义排序', 3, 1, 1),
('自定义排序', 3, 4, 2),
('自定义排序', 5, 7, 1),
('自定义排序', 5, 8, 2),
('自定义排序', 8, 2, 1),
('自定义排序', 8, 6, 2);

4. 查询嵌套结构（递归CTE） #

用PostgreSQL的递归公共表表达式（CTE）可以轻松获取任意方案的完整嵌套结构：

WITH RECURSIVE component_tree AS (
    -- 递归起点：顶级组件
    SELECT 
        c.id,
        c.name,
        ch.parent_component_id,
        ch.sort_order,
        1 AS level
    FROM components c
    JOIN component_hierarchies ch ON c.id = ch.child_component_id
    WHERE ch.hierarchy_scheme = '默认排序' AND ch.parent_component_id IS NULL
    ORDER BY ch.sort_order

    UNION ALL

    -- 递归遍历子组件
    SELECT 
        c.id,
        c.name,
        ch.parent_component_id,
        ch.sort_order,
        ct.level + 1 AS level
    FROM components c
    JOIN component_hierarchies ch ON c.id = ch.child_component_id
    JOIN component_tree ct ON ch.parent_component_id = ct.id
    WHERE ch.hierarchy_scheme = '默认排序'
    ORDER BY ct.level, ch.sort_order
)
-- 格式化输出嵌套结构
SELECT 
    REPEAT('- ', level - 1) || name AS formatted_component
FROM component_tree;

5. 优化建议 #

• 给component_hierarchies表创建联合索引：CREATE INDEX idx_hierarchy_parent_sort ON component_hierarchies (hierarchy_scheme, parent_component_id, sort_order);，提升递归查询的效率
• 如果排序方案较多、需要存储方案的额外信息（如描述、创建人），可以拆分出hierarchy_schemes表，将component_hierarchies的hierarchy_scheme字段改为外键关联该表：

CREATE TABLE hierarchy_schemes (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE component_hierarchies 
DROP COLUMN hierarchy_scheme,
ADD COLUMN scheme_id INT REFERENCES hierarchy_schemes(id) NOT NULL;

内容的提问来源于stack exchange，提问作者Charles de Dreuille