树形表结构设计
需求背景
根据一个部门查询这个部门的全部子级部门
方案 1: 正常设计
字段 | 备注 |
---|---|
id | 部门 id |
dept_name | 部门名称 |
parent_id | 上级部门 id |
这种设计方案是符合第三范式的最简单方案。可以完美描述树状结构,并且没有任何冗余数据。但缺点也是显而易见的。如果跨层级查询就要递归查询
方案 2: 冗余路径
字段 | 备注 |
---|---|
id | 部门 id |
dept_name | 部门名称 |
parent_id | 上级部门 id |
path | 从最上级到该节点的路径 以 / 分割 |
数据示例
id | dept_name | parent_id | path |
---|---|---|---|
1 | xx 科技有限公司 | /1 | |
2 | 研发部 | 1 | /1/2 |
3 | 后端 | 2 | /1/2/3 |
4 | 前端 | 2 | /1/2/4 |
5 | 产品设计部 | 1 | /1/5 |
6 | PD | 5 | /1/5/6 |
7 | UED | 5 | /1/5/7 |
在这样表设计的情况下,查询全部子集就变成了对 path 的前缀匹配。比如我要查询所有研发部的子部门就可以这样写 sql
1 | select * from dept_info where path like '/1/2%'; |
这样的设计可以比较简单的完成查询需求,但是对于插入和更新就需要重新计算path
,并且更新全部下级的path
并且path
字段不可以无限扩展,因为受到 mysql 字段大小的约束,当然可以在一开始把这个字段开的足够大。
移动部门
如果需要把产品设计部移动到研发部下面
1 | update dept_info set parent_id = '2' where id = '5'; |
方案 3: 闭包表
方案 2 对于绝大部分的场景下已经足够用了。但是如果真的需要解决无限嵌套的问题该怎么办呢。现在之所以不能无限嵌套的原因在于path
的字段长度有限。可以把path
这个字段拿出来单独拆分成一张表。
把 path 字段拍平。记录全部根节点到叶子结点的关系。
字段 | 备注 |
---|---|
id | |
ancestor | 祖先 id |
descendant | 后代 id |
depth | 层级深度 |
但是这样层级太多的时候会带来大量的关联操作。而且在新增和编辑节点的时候操作相对复杂。其实得不偿失。
本博客所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 北梦的个人博客!
评论