A design method of user, role and data range

In actual development, a situation is encountered:
A certain type of user, has a specific role, the user and the data range of the specific correspondence exists in the different tables.

For example:
User A, User B exists.
User A has role a, which can manage certain regions, certain areas, and certain departments.
User B has role B, which can manage certain regions, branch offices in certain regions, and certain departments.

The scope of management is fixed, there are large areas, regions, regional branches, departments.
Each type of user can manage several of them.

Among the tables are:
1. The regional table, which contains the region-wide and regional relations.
2. Regional Branch relations table, including the region and regional branches of the relationship.
The regional and regional branches are one level.
3. Department table, including the region, area.
4. Regional Branch, Department Association relationship table.
5. User, Large Area association relation table.
6. User, Area association table.
7. User, Regional Branch relations table.
8. User, Department relationship table.

When assigning the scope of management, a specific scope can be assigned directly to the role.
such as user A, because of the role a, can be directly assigned to the management of the region, or directly assigned to the management of the area, or directly assigned to the management department.
User B, because there is a role B, can be directly assigned to the management of the region, or directly assigned to the management of the regional branch, or directly assigned to the management department.

Because the user and scope relationships can be assigned directly, there may be areas of allocation that are not in the allocated region, and the assigned school is not under the assigned regional or regional branch.
Such as:
User A assigns the Northwest region, while assigning a northeast region under the region, while allocating a southwest region in the X region under the XX department.

There is now a business need to query the management department based on the region of choice.

The region of choice is from:
(1) If User A is logged in, have role a:
If you assign a large area of administration, the region is displayed.
If a managed region is assigned, the region where all the assigned regions are located is displayed.
If a managed department is assigned, the large area of all assigned departments is displayed.
The result takes the same set as above.
(2) If User B is logged in, have role B:
If a managed region is assigned, the region in which the area is located is displayed.
If a managed regional branch is assigned, the region where all assigned regional branches are located is displayed.
If a managed department is assigned, the large area of all assigned departments is displayed.
The result takes the above-mentioned and set.

The results displayed:
(1) If User A is logged in, have role a:
If a large area of administration is assigned, then the department is displayed, and all the large regions are the departments that select the large regions.
If a managed region is assigned, the department is displayed, all the regions are under that region, all departments.
If a managed department is assigned, the department is displayed.
The result takes the above-mentioned and set.
(2) If User B is logged in, have role B:
If a managed region is assigned, the department is displayed, and the area is under that region, all departments.
If assigned the regional branch of management, then show the department, regional branch of the table, all the regional branches for the selection of the region under, all departments.
If a managed department is assigned, the department is displayed.
The result takes the above-mentioned and set.

Then the architecture of the great God felt that if directly based on the role and the specific user query, the program flexibility will be greatly reduced.
To make the design more flexible, you intend to establish the relationship between the role and the data range.
In the search, the data range is first looked up based on the role, and then in the search, if there is a range, the query for the table that involves that range is incremented.
This avoids the need for a wide range of programs to be modified in the future as a result of increasing roles, involving these ranges of data, assigning different ranges of data, or because of the need to query some of the conditions and thus modify the data range of the existing role.

If the other great gods have any better plan, I hope you will make a lot of comments and suggestions ah.

2017/02/20 has already put
1. User, Large Area association table.
2. User, Locale association table.
3. User, Regional Branch relations table.
4. User, Department relationship table. The
is merged into a single table, and the Data range field is added to determine which type.