如何将3个结构相似层级表合并为单表？现有动物层级表设计是否合理？

一、三个层级分类表合并为单表的规范化方案 #

你提到的cat1、cat2、cat3属于典型的层级分类结构，可以通过自引用单表实现合并，核心逻辑是用一个表存储所有层级的分类，通过parent_id字段关联父级，维持一对多的层级关系。

合并后的表结构 #

CREATE TABLE categories (
    id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    name TEXT NOT NULL,
    description TEXT,
    parent_id BIGINT REFERENCES categories(id) -- 自引用外键，指向父分类ID
);

数据存储示例 #

• 原cat1的顶级分类：id=1, name='一级分类A', description='...', parent_id=NULL（无父级的顶级节点）
• 原cat2的二级分类：id=2, name='二级分类A1', description='...', parent_id=1（关联一级分类ID）
• 原cat3的三级分类：id=3, name='三级分类A11', description='...', parent_id=2（关联二级分类ID）

核心优势 #

• 无需新增表即可扩展更多层级（比如四级分类）
• 字段统一，避免重复维护相同属性
• 可通过递归CTE快速查询某分类下的所有子节点

二、动物层级表设计的评估与优化 #

原设计的正确性 #

原设计的层级逻辑是通顺的，完全符合动物组→类别→子类别→动物的业务关系，但存在严重的字段冗余问题——四个表重复维护name、description、images等通用字段，新增字段时需要修改所有表，维护成本极高。

优化方案：自引用统一表+特有字段拆分 #

将所有层级节点合并到一个统一表，用type字段区分节点类型（动物组、类别、子类别、动物），通过parent_id关联父节点；若动物存在特有字段（如age、weight），单独建表存储，避免污染分类节点。

优化后的核心表结构

-- 所有层级节点的统一表
CREATE TABLE animal_hierarchy (
    id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    name VARCHAR(100) NOT NULL,
    description TEXT NOT NULL,
    images VARCHAR(100) NOT NULL,
    type VARCHAR(20) NOT NULL CHECK (type IN ('animal_group', 'category', 'subcategory', 'animal')), -- 节点类型约束
    parent_id BIGINT REFERENCES animal_hierarchy(id)
);

-- 动物特有字段表（一对一关联统一表）
CREATE TABLE animal_details (
    id BIGINT PRIMARY KEY REFERENCES animal_hierarchy(id), -- 复用统一表ID
    age INT,
    weight NUMERIC(5,2),
    -- 其他动物专属字段
);

数据存储示例

• 动物组（哺乳类）：id=1, name='哺乳类', type='animal_group', parent_id=NULL
• 类别（猫）：id=2, name='猫', type='category', parent_id=1
• 子类别（小型猫）：id=3, name='小型猫', type='subcategory', parent_id=2
• 动物（布偶猫）：id=4, name='布偶猫', type='animal', parent_id=3
• 布偶猫特有信息：id=4, age=3, weight=4.5（存储在animal_details表）

优化后的核心优势 #

• 新增通用字段（如tags）只需修改animal_hierarchy一次
• 动物特有字段单独维护，不影响分类节点结构
• 层级扩展灵活，新增层级仅需在type的CHECK约束中添加值
• 可通过递归CTE快速查询某动物组下的所有动物

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