Quadruple Table Model

Background

In enterprises, there are complex hierarchical relationships, and users at different levels have different data permissions. The managers hope that users at different levels can only view the data under their own permissions.

Original solution: Use a permission table to record the permission information that users have on different dimensions, and configure row-level permissions in each business data table.

Note: This article uses brand and region dimensions as examples to demonstrate the permission configuration process. You can adjust the dimensions based on actual scenarios.

The example permission table:

The process of configuring permissions using the original solution:

Problems

The original solution applied in actual scenarios may encounter the following problems:

Data redundancy: There is a many-to-many relationship between users and dimension permissions, resulting in a large amount of redundant information in the permission table.

It is difficult to configure permissions dynamically with the permission table.

If a new dimension is added with n different dimension values, and the current number of rows in the permission table is m, then up to m*n records need to be inserted into the permission table.

Solution

The quadruple table model splits a permission table into a user information table, multiple dimension tables, and a dimension permission table so as to independently maintain users' permission information on different dimensions.

Compared to the original solution, the quadruple table model mainly makes the following adjustments.

1. The permission table is split into a user information table, a dimension table, and a dimension permission table.

2. Build the quadruple table model with the user information table, dimension table, dimension permission table, and business data table.

Data Preparation

Before configuring row-level permissions, you need to prepare the following types of data tables:

1. User information table: contains fields such as username, and the value of the username field is unique.

2. Dimension table: contains dimension fields, and the value of the dimension field is unique.

3. Dimension permission table: contains fields such as username and dimension, and stores user permission information on that dimension.

4. Business data table: contains fields such as dimension and stores actual business data of that dimension.

Building Model

1. For details about establishing the following two types of associations, see Setting Association.

Associate the user information table with the dimension permission table in a 1:N relationship to record the user's permissions on different dimensions.

Associate the dimension table with the business data table in a 1:N relationship for future business data queries.

2. Set the field for the login user.

3. Enable component data access for the dimension table and business data table.

For details, see Data Permission of Public Data Component.

4. Configure row-level permissions on the dimension table to obtain the dimensions that the login user has permission to access. According to the association, the relevant data for the corresponding dimension can be retrieved. Finally, view the intersection of data from multiple dimensions.

The organizational structure, functional departments, and positions in a company will often change. So how to configure data permissions for the changing parts?

Adding Dimensions

The quadruple table model is easy to expand dimensions. When adding a new dimension, you need to configure the relevant associations and configure row-level permissions on the newly added dimension table. The system will automatically retrieve the intersected data from multiple dimensions.

Procedure:

1. Prepare a dimension table and a dimension permission table.

2. Establish the following associations.

Associate the user information table with the newly added dimension permission table in a 1:N relationship.

Associate the dimension table with the relevant business data table in a 1:N relationship.

3. Configure a row-level permission on the newly added dimension table.

Adding a Business Data Table

When adding a new business data table, you do not have to configure row-level permissions. Simply associate it with the corresponding dimension table.

For example, if you add a business data table that has a logical relationship with the region dimension table, then associate it with the region dimension table.

There is a hierarchical relationship between company leaders, regional managers, and regional team members. And different permissions need to be assigned to users based on different store dimensions.

It is required that company leaders can see all data, regional managers can only see data for their corresponding regions, and regional team members can only see their own data. For details, see Multi-level Organization Authority Assignment.