在实际开发过程中,经常存在多个层级结构的设计,而且多个层级结构还需要排序。这里通过将多级结构的数据在同一张表中(无需多张表进行关联),并通过level的巧妙设计来实现单表查询。
level的设计原则:0.*.* 其中,0是顶级结构,第一个*是顶级结构下的结构,第二个*是顶级结构下的结构下的结构。
举个栗子:
1、创建部门架构表:
CREATE TABLE department (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '部门名称或下属机构名称',
`level` VARCHAR(255) NOT NULL DEFAULT '0' COMMENT '部门级别字符串',
`parent_id` BIGINT UNSIGNED NOT NULL COMMENT '父级主键id',
`seq` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '排序号',
`mtime` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '更新时间',
`ctime` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '创建时间',
`muser_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '修改人',
`cuser_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '创建人',
PRIMARY KEY(`id`),
KEY `idx_level` (`level`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT '部门架构表';
2、生成如下数据
3、业务图对应如下
level说明:
UE(id是6)是属于前端开发(id是3),而前端开发是属于技术组(id是1),因此,UE的level是0.1.3 依次为顶级的id到最靠近自己的id。
使用场景:
比如:
(1)如果想查询所有技术组下一级的架构,可以用如下sql语句:
SELECT * FROM department WHERE `level` LIKE '0.1';
(2)查询所有技术组下所有的架构包括下一级的下一级等等
SELECT * FROM department WHERE `level` LIKE '0.1%';
————————————————
版权声明:本文为CSDN博主「timchen525」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/timchen525/article/details/81175224
其他思路
组织机构树数据库表设计
公司需要做一个组织机构管理的系统,但是现有的数据库中存储的方式,机构之间的关联只是通过parent_id关联的,这样在查询的时候需要不断的递归查询表数据,性能很差,逻辑也不清晰。所以在网上找到了几种针对这种树状结构存储,查询插入的优化方法
3.组织机构树设计
1.2两种有点复杂了,第三个连接有一位答主介绍了一种快捷查询的方法
1 很麻烦的做法。
2 简单的只需要在原表里加一列就行了:
3
4 组织机构简洁字段设计:
5
6 (ogran_code是组织机构唯一代码,真正的系统里都会有这东西的)
7
8 id,name,ogran_code,parent_id
9
10 快速查询字段设计:
11
12 id,name,ogran_code,parent_id,code_link
13
14 (code_link是从根到该机构的整个code链条,例如: "root_code"+"first_code"+"child_code")
15 分隔符自定义即可
16 添加编辑机构时只关注该机构的父机构,在父机构的link上添加本机构的部分: "pareat_code_link"+"local_code"
17
18 任何查询都可以通过这个字段快速完成。
19
20 1,某机构所有子机构,查询所有包含某机构CODE的CODE_LINK即可。可以使用like,超级简单。
21 2,查询Level,拆分该字段即可。
22
23 总之,很方便记录的一个链路LINK,可以做到任意需要递归才可以完成的查询。
用图表分析了一下
- 当插入(1总公司)
id code_link
1 0_null_null
- 在(1总公司)下插入(2上海分公司),总公司的child_code为2,上海分公司的root_code为1
id code_link
1 0_null_2
2 1_null_null
- 在(1总公司)下插入(3深圳分公司),总公司的child_code为2,3,上海分公司的root_code为1
id code_link
1 0_null_2,3
2 1_null_null
3 1_null_null
- 在(2上海分公司)下插入(4徐汇办事处),上海分公司的child_code为4,徐汇办事处的first_code为2
id code_link
1 0_null_2,3
2 1_null_4
3 1_null_null
4 1_2_null
- 在(2上海分公司)下插入(5闵行办事处),上海分公司的child_code为4,5,闵行办事处的first_code为2
id code_link
1 0_null_2,3
2 1_null_4,5
3 1_null_null
4 1_2_null
5 1_2_null
- 在(3深圳分公司)下插入(6人事部),深圳分公司的child_code为6,人事部的first_code为3
id code_link
1 0_null_2,3
2 1_null_4,5
3 1_null_6
4 1_2_null
5 1_2_null
6 1_3_null
- 在(3深圳分公司)下插入(7财务部),深圳分公司的child_code为6,7,财务部的first_code为3
id code_link
1 0_null_2,3
2 1_null_4,5
3 1_null_6,7
4 1_2_null
5 1_2_null
6 1_3_null
7 1_3_null
- 在(4徐汇办事处)下插入(8研发部),徐汇办事处的child_code 为8,研发部的first_code为2,4(带上徐汇办事处的first_code 2)
id code_link
1 0_null_2,3
2 1_null_4,5
3 1_null_6,7
4 1_2_8
5 1_2_null
6 1_3_null
7 1_3_null
8 1_2,4_null
- 在(4徐汇办事处)下插入(9市场部),徐汇办事处的child_code 为8,9,市场部的first_code为2,4(带上徐汇办事处的first_code 2)
id code_link
1 0_null_2,3
2 1_null_4,5
3 1_null_6,7
4 1_2_8,9
5 1_2_null
6 1_3_null
7 1_3_null
8 1_2,4_null
9 1_2,4_null
- 在(9市场部)下插入(10市场调研小组),市场部的child_code为10,市场调研小组的first_code为2,4,9(带上市场部的first_code)
id code_link
1 0_null_2,3
2 1_null_4,5
3 1_null_6,7
4 1_2_8,9
5 1_2_null
6 1_3_null
7 1_3_null
8 1_2,4_null
9 1_2,4_10
10 1_2,4,9_null
至此,查询一个机构的子机构只需查询root_code和first_code中含有此节点id的数据
例如,查询(2上海分公司)的子机构,则为4,5,8,9,10
查询(4徐汇办事处)的子机构,则为8,9,10
转载于:https://www.cnblogs.com/blog-cq/p/10733011.html