层级类别关联wallets_balance_history的数据库Schema优化咨询

核心设计思路 #

针对你的需求，推荐采用自关联类别表+单一外键关联的方案，既满足当前三级类别（父+1级+2级）的关联规则，又具备良好的扩展性，同时查询逻辑简单。

1. 类别表（categories）结构调整 #

让类别表自身维护层级关系，字段如下：

• category_id：主键，唯一标识类别
• parent_id：外键，关联本表category_id，顶级父类的parent_id设为NULL
• name：类别名称
• level（可选）：标记类别层级（0=父类，1=1级子类，2=2级子类），用于快速筛选

2. 交易历史表（wallets_balance_history）修改 #

仅新增一个外键字段：bottom_category_id，关联categories.category_id，允许为NULL。

规则约束实现 #

通过数据库约束或业务逻辑确保：

• 若bottom_category_id不为NULL，必须保证该类别向上的父链完整（比如2级子类必须有对应的1级父类，1级子类必须有对应的顶级父类）
• 不同关联场景的赋值规则：
  • 无类别关联：bottom_category_id设为NULL
  • 仅关联父类：bottom_category_id设为父类的category_id
  • 关联父类+1级子类：bottom_category_id设为1级子类的category_id
  • 关联父类+1级+2级子类：bottom_category_id设为2级子类的category_id

查询实现（无需复杂条件） #

要获取某条交易历史的所有关联类别，用递归CTE即可一次性拉取完整父链，语句通用且简洁：

WITH RECURSIVE category_chain AS (
    SELECT category_id, parent_id, name, level
    FROM categories
    WHERE category_id = (SELECT bottom_category_id FROM wallets_balance_history WHERE history_id = [目标记录ID])
    UNION ALL
    SELECT c.category_id, c.parent_id, c.name, c.level
    FROM categories c
    JOIN category_chain cc ON c.category_id = cc.parent_id
)
SELECT * FROM category_chain;

如果是固定三级场景，也可以用简单的多表关联替代递归：

SELECT 
    c0.category_id AS parent_id, c0.name AS parent_name,
    c1.category_id AS level1_id, c1.name AS level1_name,
    c2.category_id AS level2_id, c2.name AS level2_name
FROM wallets_balance_history wbh
LEFT JOIN categories c2 ON wbh.bottom_category_id = c2.category_id
LEFT JOIN categories c1 ON c2.parent_id = c1.category_id
LEFT JOIN categories c0 ON c1.parent_id = c0.category_id
WHERE wbh.history_id = [目标记录ID];

方案优势 #

• 结构简洁：无需额外桥接表，仅靠自关联和单一外键实现，符合数据库设计范式
• 扩展性强：未来新增更多层级类别，只需在categories表中添加数据，无需修改wallets_balance_history结构
• 查询简单：递归CTE语句固定，无需复杂的条件判断，适配所有层级场景

与原有方案对比 #

• 优于方案1：不会因层级增加而被迫添加新字段，避免表结构膨胀
• 优于方案2：省去额外的桥接表，减少数据冗余和维护成本

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