Organizational Structure New Data Structure Thinking

1 Introduction

The permission structure has always been an inescapable function of small and medium-sized systems in the enterprise. In this article, the space involved is limited to small and medium-sized systems that are not particularly complex.

Taking what I have encountered as an example, the permission structure is generally divided into organization management, role management, and user management.

Organization management is in charge of data permissions. An organization is isolated from each other horizontally, and visibility is determined vertically.

Role management is in charge of functional permissions. Different roles have different menus and different functions.

User management is a common manifestation of organizations and roles.

Here, the key point is organizational management, because in different systems, the meanings of roles themselves are very different. Roles may or may not be managed by superiors and subordinates. Maybe the menus you can see are different, or you The buttons you see are different, and the embodiment of these functions needs to be designed according to the different front-end architectures. It can be said that there is no accurate design that can cover the meaning of the role functions.

However, organizations generally have more commonality, and the relationship between the organization and the front-end is not very deep. Generally, only one management function is required, and users can link to the organization. It mainly depends on how the back-end handles data queries under different organizational relationships.

2 General form of organizational structure data structure

In general systems, most of the design organizational structures use a table, use pid as a foreign key to associate the id of this table, and use the progressive association between pid and id.

Roughly similar to the following table

id	name	pid
0	top organization	null
1	two-tier organization	0
2	three-tier organization	1

In this way, we null , and find the sub-records of the second-level organization through the record with pid of 1. If we want to find the third-level organization, we also need to use complex SQL to reorganize the data relationship to facilitate finding. A chain of organizational relationships.

For example, this sql in oracle, such sql can be used to find all the associations of the top-level organization.

SELECT * FROM table_organ
            START WITH id = '0'
            CONNECT BY NOCYCLE PRIOR ID = pid

This is not a big problem. If it is limited to the query of the organizational relationship table, then our problem will never need to be raised.

But for the query of the associated table, this method is a bit cumbersome. If you want to query the data of the top-level organization and its subordinate organizations in a certain table, you need to use the following sql:

select * from some_table where organId in (SELECT id FROM table_organ
            START WITH id = '1'
            CONNECT BY NOCYCLE PRIOR ID = pid)

There is no problem with this implementation. The current database must have taken into account that the subquery will only be queried once under the condition that the conditions remain unchanged. That is to say, the side effect of such a query will only result in one more query.

But after many such functional designs, the old situation has gradually become unsatisfactory for me.

3 New forms of organizational data structures

It is not necessary to use vertical data relationships to express organizational levels, and horizontal data relationships can also be used.

What is vertical and horizontal? Let's explain.

3.1 Longitudinal data relationship table

The vertical data relationship is represented in the following table. It can be seen that the level of the hierarchical relationship is represented by increasing the depth of the hierarchy.

id	pid
0	null
1	0
2	1

3.2 Horizontal data relationship table

What about using a horizontal data relational representation? See the table below. In the following table, the hierarchical relationship is represented by the length of the horizontal expansion data, and the level of the hierarchical relationship depends on the length of the horizontal data. (order_seq represents its own order under the current level, in order to facilitate data processing, tier is the level of data, whether it is required depends on whether the actual scene can be used, for example, when you want to be able to query data of a specified depth)

id	authority	order_seq	tier
0	0/	0	0
1	0/1/	1	1
2	0/1/0/	0	2

3.3 Design points of data structure

3.3.1 Query method

After taking advantage of horizontally engineered data, queries are surprisingly easy.

When I wish to query all associations of the top-level organization:

SELECT * FROM table_organ where authority like '0/%'

When I wish to query the data belonging to the top-level organization:

select * from some_table where authority like '0/%'

When I want to query through the middle layer of organization and find the data of all the upper and lower organizations:

select * from some_table where authority like '0/1/%' or instr('0/1/', authority) = 1

3.3.2 Separator

In the horizontal data relationship table in 3.2 above, you can see that I am using a split of / , and the data should also / separator.

This is to prevent abnormal data judgment caused by too much data in the next layer, such as 0/1 and 0/10 . In the above sql, if there is no / , the query result will have abnormal data.

3.3.3 Selection of numbers

In this design, the correct choice of numbers is crucial, and it is the basis of the entire data structure.

The following two points need to be observed:

1. Duplicate numbers are not allowed in siblings with the same superior, and duplicate numbers can appear in siblings with different superiors, such as 0/0/ and 1/0/
2. Try not only to increase the numbers, but to fill vacancies before, for example, you have 0/ , 1/ , 3/ three top-level organization, when 1/ after being deleted, re-add needs to be able to fill 1 vacancies

Therefore, a sql is designed to help us achieve this requirement, and sql is the version of mysql.

select *
from (select *
      from (select c.rownum
            from (SELECT @rownum := @rownum + 1 as rownum, a.order_seq
                  from (select *
                        from cem_organ
                        where authority regexp '^[0-9]+/$') a,
                       (SELECT @rownum := -1) b
                  ORDER BY a.order_seq) c
            where c.rownum != c.order_seq
            limit 1) as e
      UNION
      select *
      from (
               select max(d.order_seq) as num
               from cem_organ d
               where d.authority regexp '^[0-9]+/$') as f
      where f.num is not null) as g
limit 1

This sql is divided into three parts.

1. The first part is to find the missing numbers, the idea is to use the unequal records order_seq and row_num
2. The second part is to find the largest number.
3. Finally, the results of the first part and the second part are combined, and only the previous one is taken. If the sql does not return any records, the program takes 0.

Use this sql, we should also note that the use of the authority to restrict your superiors through positive, if the top-level organization, is ^[0-9]+/$ , if non-top-level organization, is ^0/[0-9]+/$ , so ^0/1/[0-9]+/$ . (The same effect can also be achieved in the form of like)

There is also a key issue that needs to be paid attention to here, that is, the problem of concurrency. If this sql is executed at the same time, it will cause the same value. Therefore, it is necessary to add a distributed lock where the sql is executed to ensure concurrency. A unique value can be obtained in each case. The value of the lock uses the parent authority to improve some performance.

3.4 Advantages

3.4.1 The query logic is simple and easy to transplant

This advantage is obvious, when you want to find all the subordinate relationships in the organizational relationship, the query logic only needs to use a like , and like basically all the database syntax is the same, because it is simple, it is easy to transplant.

regex query.

3.4.2 Fast query speed

The advantages are particularly obvious when there are millions of data levels, especially when the query condition is that there are many top-level organizations that belong to subordinates.

In addition, there is an unparalleled advantage in querying between two or more subordinate and subordinate relationships.

For example, when the organization is in charge of the department, and the department is in charge of the printer, when you apply the same idea to the department table and the printer table, you can skip the organization and department and directly query.

select * from table_printer where authority like '0/%'

Here, you can think about pid association method, especially in a large organizational relationship, when the amount of data in the department exceeds a thousand, how to crack the database in , also needs to be considered The place.

3.4.1 Data is easier to understand

Compared with the previous pid connection method, authority can more intuitively reflect the relationship between data.

3.5 Disadvantages

3.5.1 Complex implementation logic

As can be seen from the previous article, in order to realize this kind of relationship between superiors and subordinates based on data structure, we need to meet many conditions, and we need to carefully maintain the authority representing the relationship between superiors and inferiors in various places. Once an error occurs, the impact will be is huge.

3.5.2 Not suitable for particularly complex logic

At present, I have not encountered any requirements that may surpass this data structure, but it is inevitable that customers will put forward alternative requirements from some tricky angles, and this data structure is fundamentally very delicate and fragile. Once there is a change , it is likely to increase the complexity of the implementation logic, or even to reinvent the wheel.

Therefore, the pros and cons of such data need to be carefully weighed.

At last

This new type of data structure has been tempered and verified, and it does actually help you solve some business pain points.

But it is also a double-edged sword. To be able to control it completely requires strong logical thinking and code design ability.

Otherwise, the final code structure will appear to be filled with patches to implement this logic. Therefore, continuous refactoring, code reduction, and perfect regression testing are also very necessary.