Shulou(Shulou.com)05/31 Report--

This article focuses on "how to solve the design of user rights database in complex systems". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to solve the user rights database design in complex systems.

Requirement statement

People with different responsibilities should have different permissions for system operation. Excellent business system, this is the most basic function.

You can assign permissions to groups. For the business system of a large enterprise, it is time-consuming and inconvenient to require administrators to assign system operation rights to their employees one by one. Therefore, the concept of "group" is put forward in the system, people with the same permissions are grouped into the same group, and then the permissions are assigned to the group.

The rights management system should be extensible. It should be able to be added to any system with rights management capabilities. Just like components, they can be constantly reused, instead of being redeveloped for the rights management part every time a management system is developed.

Meet the functional permissions in the business system. In the traditional business system, there are two kinds of rights management, one is the management of functional permissions, and the other is the management of resource permissions, between different systems, functional permissions can be reused, while resource permissions can not.

About Design

With the help of the action programming concept of NoahWeb, in the design stage, system designers do not need to consider the design of the program structure, but start from the program flow and database structure. In order to meet the requirements, the design of the database is very important, whether it is the concept of "group" operation or the reusability of the whole set of rights management system, it all depends on the design of the database.

Let's first analyze the database structure:

First of all, the action table (hereinafter referred to as the "permissions table"), the gorupmanager table (hereinafter referred to as the "management group table"), and the master table (hereinafter referred to as the "personnel table") are three physical tables, which record the information of "permissions", "management groups" and "people" in turn. As shown below:

The relationship between these three tables is many-to-many, a permission may belong to multiple administrative groups at the same time, or an administrative group may contain multiple permissions at the same time. By the same token, a person may belong to multiple administrative groups at the same time, and a single administrative group may contain multiple people at the same time. As shown below:

Since there is a many-to-many relationship between these three tables, it is best to use the other two tables to complete the interaction between them. These two tables act as mappings, namely "actiongroup" table (hereinafter referred to as "permission mapping table") and "mastergroup" table (hereinafter referred to as "personnel mapping table"). The former maps the interaction between permissions table and management group table. The latter maps the interaction between the personnel table and the administrative group table. As shown below:

In addition, a table is needed to control the permission column in the left menu when the system is running, that is, the permission column table, as shown below:

According to the above analysis, we design the database structure, as shown in the following figure:

Click here to view the data table field design of the rights management system

In order to carry out good analysis, we split the database structure diagram, the role of the three physical tables is very clear, now let's take a look at the role of the two mapping tables.

A permission mapping table is shown below:

First, let's take a look at the field associations between the permission mapping table and the administrative group table and the permissions table.

Look at the red circle in the figure, and first look at the association of the gorupid field. This association appears in the actual database as shown in the following figure:

As shown in the figure, the groupid of the Super Admin in the administrative group table is 1, so the permission of groupid 1 in the permission mapping table is the right that the Super Admin has.

The groupid field association is used to find out what permissions an administrative group can perform. However, the details of these permissions are queried by the action field association.

The association of action fields in the database looks like the following figure:

Through this association, the details of those permissions in the permission mapping table are queried. Taken together, we know what permissions an administrative group can execute, and what are the details of those permissions.

You might ask, why not use the actionid field to associate it? Because:

The id field in the permissions table may change after multiple database operations.

Only the permissions that can be executed by an administrative group are recorded in the permission mapping table.

Once the id in the permissions table changes, the records in the permission mapping table also change.

It is highly undesirable that the permissions that can be executed by an administrative group are bound to go wrong.

Given the above, you should use the action field to associate it, because:

In the permission table, the id may change, while the action field cannot change under any circumstances.

The action field of the record in the permission mapping table will not change.

The permissions that can be executed by an administrative group will not go wrong.

The personnel mapping table is shown below:

Let's take a look at the field associations between the personnel mapping table and the administrative group table and the personnel table, as shown in the following figure:

Look at the red circle section in the figure, and first look at the groupid field association, which is shown in the database as shown in the following figure:

As shown in the figure, the groupid of the "Super Admin" group is 1. Let's look at the personnel mapping table. Admin belongs to the Super Admin group, while administrator belongs to the Super Admin group as well as the administrators group.

This association is used to find out who is in an administrative group. As above, the details of the person are queried by association with the id field (the masterid field in the personnel mapping table).

The id field (the masterid field in the personnel mapping table) is shown in the database as shown below:

A person may belong to multiple "administrative groups" at the same time, as shown in the figure, administrator belongs to two "administrative groups" at the same time. Therefore, there will be two records for administrator in the personnel mapping table.

Only in this way can we query the details of the people in the management group. Taken together, you can know who is in a management group, as well as the details of that person.

Combined with the permission table and permission mapping table mentioned above, the "group" operation in the requirement is implemented, as shown in the following figure:

In fact, the administrative group table records only the basic information of the group, such as name, group id, and so on. The details of the people in a group and the permissions that the group can perform are recorded in the personnel table and the permissions table. It is the two mapping tables that really record who a group has and what permissions it can perform. Through the convergence of the two mapping tables, the interaction between the three physical tables can be realized, thus completing the "group" operation mentioned in the requirements.

Let's take a look at the interaction between the permission column table and the permission table. The field association between the two tables is shown in the following figure:

The two tables are associated with the actioncolumnid field, which looks like the following figure in the database:

As shown in the figure, through this association, we can see very clearly which column the permissions in the permission table belong to.

Now that the database structure is clear, the ability to assign permissions and "group" operations have been implemented. Let's analyze the reusability of the rights management system mentioned in the requirements.

Why can systems built with this kind of database design be reused?

Three physical tables record three decisive elements in the system. "permissions", "groups" and "people". These three elements can be added at will and will not be affected by each other. No matter what type of business system, these three decisive elements will not change, which means that the structure will not change, but only the data.

The relationship between the three elements is recorded in the two mapping tables. However, these relationships are completely man-made, and when they need to change, they only operate on the records in the database without changing the structure.

The permissions column table records the columns displayed when the system is in use. Whether you want to add columns, modify columns, or reduce columns, they are just operational records.

To sum up, if the database is designed in this way, the system can be reused and can withstand the test of "change".

Summary:

The key point of this system is that the three physical tables firmly grasp the core components of the system, while the two mapping tables perfectly map the interaction between the three physical tables. The difficulty is to understand the work of the mapping table, which records relationships, and implements the concept of "group" operations. The overall design of the system is based on the idea that it can be "reused" in different MIS systems to meet the functional permission settings of different systems.

Appendix:

Field Design of data Table in Rights Management system

Let's take a look at the database table design of the rights management system, which is divided into six tables, as shown in the following figure:

Action table:

All the actions in the system and the related descriptions of the actions are recorded in the action table.

Actioncolumn table:

Actioncolumn table records the action of the sub-column, when the system is running, the left menu bar provides several different functions, each piece is a sub-column, each add a sub-column, the table will add a record, correspondingly, the left menu bar will also add a new column.

Actiongroup table:

The actiongroup table records the group in which the action is located.

Groupmanager table:

The groupmanager table records information about administrative groups, and each time an administrative group is added, a record is added here.

Mastergroup table:

The mastergroup table records the administrative group in which the administrator belongs, and because an administrator may belong to multiple groups at the same time, there may be multiple records for an administrator in this table.

Master table:

The master table records the information of all administrators, and each time an administrator is added, a record is added to the table.

At this point, I believe you have a deeper understanding of "how to solve the user rights database design in complex systems". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.