JSON树形非扁平化数据转关系型数据库模型的推荐表示方案

好的，咱们来拆解这个问题——把你这种嵌套递归的JSON树形数据转换成关系型数据库模型，最实用的方案得结合数据结构和后续业务场景来选。先看你的JSON结构：顶层是一个日期+一组可递归嵌套的记录/成员，每个节点（不管是顶层record还是子member）都可能带title（可选）、label、value，还能包含子成员。

核心思路：统一节点实体+邻接表维护层级 #

最通用、易维护的方案是用邻接表模式，把所有递归的节点（record和member）统一成一个实体，用父ID关联层级，再加上一个批次表来存储顶层的日期信息。

1. 表结构设计 #

（1）批次表：record_batches #

用来存储顶层的日期，把同一天的所有记录归为一个批次：

（2）节点表：record_nodes #

存储所有的record和member，用parent_id维护树形层级：

2. 示例数据映射 #

拿你给出的JSON示例来对应插入：

1. 先插入批次：

   INSERT INTO record_batches (record_date) VALUES ('2017-02-02'); -- 转成YYYY-MM-DD格式
   -- 假设得到batch_id=1
   

2. 插入顶层record：

   INSERT INTO record_nodes (batch_id, parent_id, title, label, value)
   VALUES (1, NULL, 'Title name', 'Label name', 'Value');
   -- 得到node_id=1
   

3. 插入第一个子member：

   INSERT INTO record_nodes (batch_id, parent_id, label, value)
   VALUES (1, 1, 'string', 'string');
   -- 得到node_id=2
   

4. 插入带子成员的第二个member：

   INSERT INTO record_nodes (batch_id, parent_id, title, label, value)
   VALUES (1, 1, 'Second title', 'Label', 'Value');
   -- 得到node_id=3
   

5. 插入这个member的子成员：

   INSERT INTO record_nodes (batch_id, parent_id, label, value)
   VALUES (1, 3, 'string', 'string');
   -- 得到node_id=4
   

3. 邻接表的优缺点&适用场景 #

优点： #

• 结构简单，新手也能快速理解和实现
• 增删改节点超级方便，只需要修改parent_id或者新增/删除行
• 配合数据库的递归CTE（MySQL 8.0+、PostgreSQL、SQL Server都支持），可以轻松查询整棵树、子树或者路径

比如查询某个节点的所有子节点（以node_id=1为例）：

WITH RECURSIVE node_tree AS (
  SELECT * FROM record_nodes WHERE node_id = 1
  UNION ALL
  SELECT n.* FROM record_nodes n
  JOIN node_tree nt ON n.parent_id = nt.node_id
)
SELECT * FROM node_tree;

缺点： #

• 如果树形层级特别深（比如超过10层），递归查询的性能可能会略有下降，但大部分业务场景下完全够用
• 无法直接快速获取节点的深度或者路径（不过可以通过递归CTE计算）

4. 其他可选方案（按需选择） #

如果你的业务场景比较特殊，也可以考虑这些方案：

• 路径枚举：给record_nodes加一个path字段（比如"/1/3/4"），直接存储从根节点到当前节点的ID路径。查询子树很方便，但修改节点时需要更新所有子节点的path，适合层级固定、很少修改的场景。
• 嵌套集：用left和right字段标记节点的范围，查询整棵树的速度极快，但增删改节点需要调整大量节点的left/right值，适合查询频繁、修改极少的场景。
• 闭包表：额外建一张node_ancestors表，存储所有节点的祖先-后代关系（包括自身）。查询非常灵活，但数据冗余大，维护成本高，适合复杂的树形查询需求。

总结 #

如果你的业务是常规的增删改查，邻接表模式绝对是最优选择——简单易维护，配合递归CTE完全能满足树形数据的查询需求。

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