分享一个避免递归的部门设计方法

部门常见需求

PM添加以下需求:
(1)查找某个部门的某个level的父级部门
(2)找所有的子孙部门节点
(3)找所有的子孙部门节点数量
(4)判断是否是叶子部门节点
(5)新增部门
(6)删除部门

效率问题描述

通常部门parent结构设计

通常部门结构如下图
部门架构
一般采用parent_id的方式去表达上级部门,因此表结构设计如下:

CREATE TABLE `ts_dept_parent_struct` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '部门id',
  `parent_id` int(11) NOT NULL DEFAULT '0' COMMENT '上级部门id,没有上级部门为0',
  `dept_name` varchar(100) NOT NULL COMMENT '部门名称',
  `level` int(4) NOT NULL DEFAULT '1' COMMENT '部门层级,顶级部门为1',
  `is_leaf` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否是叶子部门 0-不是 1-是',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8 COMMENT='部门表-parent_id结构'

各节点数据值如下:

idparent_iddept_namelevelis_leaf
10董事长10
21总经理20
32产品部30
42行政总监30
53设计41
63研发部门40
74财务部40
84行政部41
97出纳51
107会计51
116架构部51
126码农部51
132外包产品部30
1413生日部41
1513摸鱼部40
1615闲聊部51
1715奶茶部51

此处提供SQL插入语句以便测试:

INSERT INTO test.ts_dept_parent_struct (id,parent_id,dept_name,`level`,is_leaf) VALUES
	 (1,0,'董事长',1,0),
	 (2,1,'总经理',2,0),
	 (3,2,'产品部',3,0),
	 (4,2,'行政总监',3,0),
	 (5,3,'设计',4,1),
	 (6,3,'研发部门',4,0),
	 (7,4,'财务部',4,0),
	 (8,4,'行政部',4,1),
	 (9,7,'出纳',5,1),
	 (10,7,'会计',5,1);
INSERT INTO test.ts_dept_parent_struct (id,parent_id,dept_name,`level`,is_leaf) VALUES
	 (11,6,'架构部',5,1),
	 (12,6,'码农部',5,1),
	 (13,2,'外包产品部',3,0),
	 (14,13,'生日部',4,1),
	 (15,13,'摸鱼部',4,0),
	 (16,15,'闲聊部',5,1),
	 (17,15,'奶茶部',5,1);

这样的方式看起来很直观,但是如果数据量一大或者部门的层级很多,就会影响效率。

实现方法及缺点

首先来说说以上需求的实现方法:

找所有的子孙部门节点

找所有的子孙部门,根据以下sql查询,然后收集所有的deptId作为parentid,递归查询,直到集合为空

select tdps.* from ts_dept_parent_struct tdps where tdps.parent_id = #{deptId}

查找某个部门的某个level的父级部门

找某个level级别的父级部门,根据以下sql查询后,判断level是否等于请求中指定的level,是则返回,否则继续往上查询

select tdps.* from ts_dept_parent_struct tdps where tdps.id = #{parentId}

找所有的子孙部门节点数量

这个可以根据上面查所有子孙部门的SQL查找,然后收集所有的deptId作为parentid,再次查询,直到集合为空,统计所有子部门的数量

select tdps.* from ts_dept_parent_struct tdps where tdps.parent_id = #{deptId}

判断是否是叶子节点

如果不添加冗余字段,那么就要进行额外的查询,判断是否有parent_id=当前部门节点的部门,没有则是叶子节点,有则不是叶子节点。

这里我更加推荐是添加一个is_leaf的字段,标志当前部门是否是叶子节点,虽然是维护起来就要多一个字段,但是却少了一次查询,我觉得值得

新增部门/删除部门

比较简单,新增直接插入即可,不过删除部门还要改动一下parentId才行。这里要看产品怎么设计。

遍历方法

表结构设计

CREATE TABLE `ts_dept_point_struct` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '部门id',
  `dept_name` varchar(100) NOT NULL COMMENT '部门名称',
  `lft` int(11) NOT NULL COMMENT '部门左值',
  `rgt` int(11) NOT NULL COMMENT '部门右值',
  `level` int(4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 COMMENT='部门表-指针结构';

设计图如下图:
指针结构图
或许看上去就会比较复杂,可以先按线条序号走一遍,其实就是先序遍历树(我相信大家都接触过),
同时,节点上去掉了parent_id,使用左值和右值代替,按照上面的走法,从1开始,给每个节点加上左值,然后遇到叶子节点的话,就给右值赋值,然后再遍历兄弟节点,兄弟节点遍历完再回到上级节点给右值赋值。

遍历完后,各节点数据值如下:

iddept_namelftrgtlevel
1董事长1341
2总经理2332
3自营产品部3123
4行政总监13223
5设计部454
6研发部门6114
7架构部785
8码农部9105
9财务部14194
10行政部20214
11出纳15165
12会计17185
13外包产品部23323
14生日部24254
15摸鱼部26314
16闲聊部27285
17奶茶部29305

这里提供一下插入语句方便测试:

INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(1, '董事长', 1, 34, 1);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(2, '总经理', 2, 33, 2);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(3, '自营产品部', 3, 12, 3);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(4, '行政总监', 13, 22, 3);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(5, '设计部', 4, 5, 4);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(6, '研发部门', 6, 11, 4);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(7, '架构部', 7, 8, 5);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(8, '码农部', 9, 10, 5);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(9, '财务部', 14, 19, 4);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(10, '行政部', 20, 21, 4);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(11, '出纳', 15, 16, 5);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(12, '会计', 17, 18, 5);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(13, '外包产品部', 23, 32, 3);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(14, '生日部', 24, 25, 4);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(15, '摸鱼部', 26, 31, 4);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(16, '闲聊部', 27, 28, 5);
INSERT INTO test.ts_dept_point_struct
(id, dept_name, lft, rgt, `level`)
VALUES(17, '奶茶部', 29, 30, 5);

实现方法

查找某个部门的某个level的父级部门

可以通过左值和右值来判断,某个节点的所有父级部门,它的左值必定是小于当前部门的左值,它的右值必定是大于当前部门的右值。
因此,可通过这个sql来查找到所有的父级部门:

select tdps.*
        from test.ts_dept_point_struct tdps
        where tdps.lft < #{dept.lft}
        and tdps.rgt > #{dept.rgt}
        order by tdps.lft asc;

我这里根据左值排序的意思,其实就是从上往下给父级部门排序,这里我认为也可以用level进行排序的,而且每级只有一个部门(不可能有两个父亲部门)

然后遍历查找出来的部门,找到指定的level即可,或者在sql中指定level,相比于之前parent的结构,可以避免了递归查询。

找所有的子孙部门节点/找所有的子孙部门节点数量

可以通过左值和右值来判断,某个节点的所有子孙部门,它的左值必定是大于当前部门的左值,它的右值必定是小于当前部门的右值。

select tdps.*
        from test.ts_dept_point_struct tdps
        where tdps.lft &gt; #{dept.lft}
          and tdps.rgt &lt; #{dept.rgt}

这个查询只需要一次查询,就可以查出所有子部门

找所有的子孙部门节点数量

判断数量不必select count(*)查询子孙部门,可以用公式来计算
子孙部门数量 = (右值-左值-1)/2,可以找上面的图验证一下,这里省略了一个sql查询

例如:
摸鱼部 下有两个部门
(31-26 -1) /2= 2

判断是否是叶子部门节点

可以看上面的图分析,叶子部门的特点,就是左值+1 = 右值
例如:
奶茶部 : 30-29 = 1 是叶子部门
摸鱼部: 31-26 >1 不是叶子部门

新增部门

对于新增单个部门,例如在摸鱼部下增加一个零食部,新增请求里面必须要指定父级部门是哪个

select tdps.*
        from test.ts_dept_point_struct tdps where id = #{parentId}

然后,得到父级部门的右值parentRgt,基于右值给新部门赋值,新部门左值为parentRgt,右值为parentRgt+1,然后所有大于等于parentRgt的值都要+2

-- 将插入的后续边缘的节点左右数+2
UPDATE ts_dept_point_struct SET lft=lft+2 WHERE lft > #{lft};
UPDATE ts_dept_point_struct SET rgt=rgt+2 WHERE rgt >= #{rgt};
-- 插入数据
INSERT INTO department(name,lft,rgt,level) VALUES('零食部',#{lft},#{rgt},level);

以上操作需要放在一个事务里面

删除部门

做这个之前,先确定方案,到底删除部门时,如果不是叶子部门时怎么办?
第一种方法:全部移除,我觉得现实生活中是比较合理的,例如在删除摸鱼部,下面的闲聊部和奶茶部也一并删除。
第二种方法:把后面的子部门移到上上级。例如在删除摸鱼部,下面的闲聊部和奶茶部放到外包产品部下。

如果是第二种方案的话,这种指针式的就不适用,只能删掉叶子部门,所以还是建议你用parent方式。因为怎么控制level的修改是一个问题,我现在还没有想到方案。

第一种方式:可以先移除这个部门下的所有子孙部门,例如删除摸鱼部:

# 查询所有子孙部门
select tdps.*
        from test.ts_dept_point_struct tdps
        where tdps.lft &gt; 26
          and tdps.rgt &lt; 31
#根据主键删除

对删除节点的后续边缘节点减2操作

/**更新节点值**/
UPDATE test.ts_dept_point_struct SET lft=lft-2 WHERE lft > 26;
UPDATE test.ts_dept_point_struct SET rgt=rgt-2 WHERE rgt > 31;

/*删除节点*/
DELETE FROM department WHERE lft=26 AND rgt=31;

个人感觉

我当前所在的公司,还是使用parent结构的,因为数据量不大,效率问题还没有显示出来。
这个方案也不是最优的,缺点是不太直观,维护起来有点麻烦,还是要看产品怎么设计界面的。
如果增加删除一个不部门,是那种弹窗式的话,那么可以考虑一下先序遍历树的方案。如果是全量推到后台,那我建议还是用回parent结构比较稳妥

引用

参考文章:https://mp.weixin.qq.com/s/GDmrIwo89WVfDyAWYSBWQA

  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值