一、背景:
1.1、使用父子节点方式的字典,不再有编码编码。在有编码结构的分级编码时代,查找一个编码的上级,可以使用反向like(例如'15501234' LIKE F_BH||'%',这样可以找到1550,15501234两个编码),查找一个编码的下级,可以使用正向like(例如F_BH LIKE '1550%',这样可以找到1550,15501234两个编码)。
1.2、查找上下级变的困难,因此增加一个非明细编码和明细编码的对应表,后来了解到闭包表,进而再次优化。
二、需求:
2.1、给定一个编码,查找它的上级和下级。
2.2、给定一个编码,查找它的所有明细下级。
三、详细设计v1.0:
3.1、字典表为Dict。新增关系表Relation,A列为所有编码值,可能是非叶子节点和叶子节点;B列只存储叶子节点。
这种可以满足部分需求:给定一个编码,查找它的所有叶子节点。
草图如下:
3.2、但是在以下查询场景下就会变得很复杂:
- 给定一个编码,查找此编码的所有下级?这种情况需要先查找到B列的叶子节点,通过叶子节点查找A列的所有父节点,根据Dict表中的级数,大于级数的编码为此编码的所有下级。实现起来有点绕。
- 给定一个编码,查找此编码的所有上级?参考上一种情况,最后找小于级数的编码为此编码的所有上级。
四、详细设计v2.0:
4.1、闭包表是树形结构的一种数据存储的方案。对应关系表中不只存储与叶子节点的对应关系。闭包表是解决分层存储一个简单而又优雅的解决方案,它记录了表中所有的节点关系,并不仅仅是直接的父子关系。
4.2、将树中任何具有祖先-后代关系的节点对都存储在表的一行中,即使这两个节点之间不是直接的父子关系,同时,我们还增加一行指向节点自己。更形象的表示可以参考图:
手画的草图:
4.3、对于查找一个编码对应的叶子节点,比原来复杂。参考5.2中的表结构,如果想查找7的叶子节点,实现如下:
select a.des from tree_path a where a.anc = 7 and a.des != 7 and
not exists (select 1 from tree_path b where b.anc=a.des and b.anc!=b.des)
4.4、实验性场景
五、参考资料:
5.1、《SQL反模式》
5.2、树形结构与关系数据库之闭包表
https://www.jianshu.com/p/2d8ac251e406
5.3、逻辑数据库设计 - 单纯的树(递归关系数据)
https://www.cnblogs.com/kissdodog/p/3297894.html
5.4、如何将树形结构存储在数据库中(方案三 Closure Table)
https://www.imooc.com/article/22133?block_id=tuijian_wz
5.5、树形结构数据存储方案(三):闭包表
https://www.biaodianfu.com/closure-table.html
5.6、数据库表设计(邻接表、路径枚举、嵌套集、闭包表)
https://www.cnblogs.com/boboxing/p/7055251.html