[php] Universal rights management database design

Permissions in the B/S system are more important than in C/S. Because the C/S system has a special client, access user permission detection can be implemented through the client or through client + server detection Implementation, and in B/S, every computer has a browser. If a complete permission detection is not established, then an "illegal user" is likely to be able to easily access the B/S system through the browser. all functions. Therefore, B/S business systems need to have one or more permission systems to implement access permission detection, so that authorized users can use authorized functions normally and legally, while those unauthorized "illegal users" will be Completely "shut out". Let's take a look at how to design a permission system that can satisfy the user function permission control in most B/S systems.

Requirement Statement

• Personnel with different responsibilities should have different permissions for system operations. Excellent business system, this is the most basic function.
• You can assign permissions to "groups". For a large enterprise's business system, it is time-consuming and inconvenient to require administrators to allocate system operation permissions to their employees one by one. Therefore, the system puts forward the concept of operating on "groups", marshaling people with the same permissions into the same group, and then assigning permissions to the group.
• Permission management system should be extensible. It should be able to be added to any system with permission management capabilities. Just like components, they can be continuously reused, instead of re-developing the permission management part every time a management system is developed.
• Meet the functional permissions in the business system. In traditional business systems, there are two types of permission management. One is the management of functional permissions, and the other is the management of resource permissions. Functional permissions can be reused between different systems, but resource permissions cannot. .

  About design

  With NoahWeb’s action programming concept, in the design phase, system designers do not need to consider the design of the program structure, but start with the program flow and the databasestructure Get started. In order to realize the requirements, the design of database is extremely important. Whether it is the concept of "group" operation or the reusability of the entire permission management system, it all lies in the design of database.

  Let’s analyze the database structure first:

  First, the action table (hereinafter referred to as the “authority table”), the gorupmanager table (hereinafter referred to as the “management group table”) , and the master table (hereinafter referred to as "personnel table"), are three entity tables, which record "authority" information, "management group" information and "personnel" information in turn. As shown below:

  

  The relationship between these three tables is many-to-many. A permission may belong to multiple management groups at the same time, and a management group may also contain multiple permissions. In the same way, a person may belong to multiple management groups at the same time, and a management group may also 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 play a mapping role, namely the "actiongroup" table (hereinafter referred to as "permission mapping table") and the "mastergroup" table (hereinafter referred to as "personnel mapping table") . The former maps the permission table. Interaction with management group tables. The latter maps the interaction between the personnel table and the management 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:

  

  Based on the above analysis, we design the database structure, as shown below:

  Click here to view the permission management system data table field design

  

  In order to be able to conduct a good analysis, we split the database structure diagram. The role of the three entity tables is already very clear. Now let’s take a look. The role of the two mapping tables.

  A permission mapping table is as shown below:

  First of all, let’s take a look at the field association between the permission mapping table, the management group table and the permission table.

  

  Look at the red circle in the picture, first look at the gorupid field correlation. The performance of this correlation method in the actual database is as follows:

  

  As shown in the figure, the groupid of "super administrator" in the management group table is 1, then the permissions with groupid 1 in the permission mapping table are also the permissions owned by the "super administrator".

  Using the groupid field association is to find out what permissions a management group can execute. However, the detailed information of these permissions is queried by the action field association.

  The performance of the action field association in the database is as follows:

  

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

  Maybe you will ask, why not use the actionid field to associate? Because:

  • The id field in the permission table may change after multiple database operations.
  • The permission mapping table only records the permissions that a management group can execute.
  • Once the id in the permission table changes, the record in the permission mapping table will also change.
  • The permissions that an administrative group can execute are bound to go wrong, which is very undesirable.

    Considering the above situation, the action field should be used to associate, because:

    • In the permission table, the id may change, but the action field is in It cannot change under any circumstances. The action field recorded in the
    • permission mapping table will not change.
    • The permissions that an administrative group can execute will not go wrong.

      The two-person mapping table is as shown below:

      Let’s take a look at the personnel mapping table and the management group table and

      The field association between

      personnel table is as shown below:

      

      Look at the red circle part in the picture first, look at the group id field association, The performance of this association method in the database is as follows:

      

      As shown in the figure, the groupid of the "Super Administrator" group is 1, let's look at the personnel mapping table, admin belongs to Super administrator group, and administrator belongs to the super administrator group and also belongs to the administrator group.

      Using this correlation method is to find out who is in a management group. As above, the detailed information of the personnel is queried by association with the id field (the masterid field in the person mapping table).

      id field (person mapping table is the masterid field). The related table in the database looks like the following figure:

      

      A person may belong to multiple "management groups" at the same time. As shown in the picture, the administrator belongs to two "management groups" at the same time. Therefore, there will be two records about administrator in the personnel mapping table.

      This correlation method can query the detailed information of the people in the management group. Taken together, we can know who is in a management group and the detailed information of this person.

      Combined with the permission table and permission mapping table mentioned above, the "group" operation in the requirement is realized, as shown below:

      

      In fact, the Management Group Table only records the basic information of the group, such as name, group id, etc. As for the detailed information of the people in a group, as well as the detailed information of the permissions that the group can perform, they are recorded in the personnel table and permission table. Two mapping tables really record who is in a group and what permissions they can perform. Through the connection of the two mapping tables, the interaction between the three entity tables can be realized, thus completing the "group" operation mentioned in the requirements .

      Let’s take a look at the interaction between permission column table and permission table. The field association between these two tables is as shown below:

      

      The two tables are related using the actioncolumnid field. The performance of this association in the database is as follows:

      

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

      Now, the database structure is very clear, the function of assigning permissions and the "group" operation have been implemented. Next, let’s analyze the reusability issues mentioned in the requirements about the permission management system.

      Why can a system built using this database design method be reused?

      • The three entity tables record the three decisive elements in the system. "Permissions", "Groups" and "People". These three elements can be added at will without affecting each other. No matter what type of business system it is, these three decisive elements will not change, which means that the structure will not change, but only the data will change.
      • The two mapping tables record the relationship between three elements. But these relationships are completely artificially created. When changes need to be made, only the records in the database are operated without changing the structure.
      • The permission column table records the columns displayed when the system is used. Whether you want to add columns, modify columns or reduce columns, it is just an operation record.

        To sum up, by designing the database in this way, the system is completely reusable and can withstand the test of "change".

        Summary:

        The key point of this system is that the three entity tables firmly capture the core components of the system, and the two mapping tables perfectly map the three Interaction between entity tables. The difficulty lies in understanding the working 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 the permission management system data table

        Let’s take a look at the database table design of the permission management system, which is divided into six tables. As shown below:

        action table:

        

        The action table records all actions in the system, as well as action-related descriptions.

        actioncolumn table:

        

        The actioncolumn table records the columns of actions. When the system is running, the left menu bar provides several different functions, each of which is one Column, every time a column is added, one record will be added in the table. Correspondingly, a new column will be added in the left menu bar. The actiongroup table records the action group.

        groupmanager table:

        

        The groupmanager table records relevant information of the management group. Every time a management group is added, one record will be added here.

        mastergroup table:

        

        The mastergroup table records the management group where the administrator belongs. Since an administrator may belong to multiple groups at the same time, the table contains There may be multiple records for an administrator.

        master table:

        

        The master table records the information of all administrators. Every time an administrator is added, a record will be added to the table.

        The above introduces [php] universal rights management database design, including aspects of the content. I hope it will be helpful to friends who are interested in PHP tutorials.