The application of bitmap method in database design: How does one data table field represent multiple business meanings

Welcome to pay attention to the public account “JAVA Front” to view more wonderful sharing articles, mainly including source code analysis, practical application, architecture thinking, workplace sharing, product thinking and so on, at the same time, welcome to add my wechat “JAVA_front” to communicate and learn together

1. Demand Background

In the system, there are three roles for users: ordinary user, administrator, and super administrator. Now we need to design a user role table to record such information. It is not difficult for us to design the following scheme:

id	name	super	admin	normal
101	The user a	1	0	0
102	The user 2	0	1	0
103	The user three	0	0	1
104	User four	1	1	1

We use 1 to indicate yes and 0 to indicate no. Then it is not difficult to observe the above table that user 1 has the super administrator role, user 2 has the administrator role, user 3 has the common user role, and user 4 has three roles at the same time.

What if a new character is added? Add a new field:

id	name	super	admin	normal	new_role
101	The user a	1	0	0	0
102	The user 2	0	1	0	0
103	The user three	0	0	1	0
104	User four	1	1	1	1

2 Discovering problems

According to the above table design function is no problem, the advantage is easy to understand the structure is clear, but we think there is no problem? The author has encountered the following problems:

In complex business environments, a piece of data may be used in different scenarios. For example, the data stored in the MySQL database may also be used in the following scenarios:

Retrieving data requires a copy to be synchronized to ES
The business side uses this table to calculate business metrics through Flink
The business side subscribes to this table Binlog message for business processing

If the table structure changes, the data sources need to be reconnected and the business side needs to make code changes, which can be very expensive to develop. Is there a way to avoid such problems?

3 Solution

We can use bitmaps so that the same field can represent multiple meanings. First, design the following data table, leaving the userFlag field blank.

id	name	user_flag
101	The user a	no
102	The user 2	no
103	The user three	no
104	User four	no

We use bitmaps, where each bit represents a role

We use bitmap to represent the following data table

id	name	super	admin	normal
101	The user a	1	0	0
102	The user 2	0	1	0
103	The user three	0	0	1
104	User four	1	1	1

The decimal value of the user bit diagram is 4

The decimal value is 2

The user trigram has the decimal value 1

The decimal value is 7

At this point we can complete the data table

id	name	user_flag
101	The user a	4
102	The user 2	2
103	The user three	1
104	User four	7

4 bitmap method detailed solution

In this chapter, we analyze some key nodes of bitmap scheme.

4.1 Enumeration Definitions

Instead of defining enumerations as numbers like 1, 2, and 4, define them in displacement so that the user can understand the designer’s intent.

/** * User role enumeration **@authorPublic number JAVA Front * */
public enum UserRoleEnum {

    / / 1 - > 00000001
    NORMAL(1."Ordinary user"),

    / / 2 - > 00000010
    MANAGER(1 << 1."Administrator"),

    / / 4 - > 00000100
    SUPER(1 << 2."Super Administrator");private int code;
    private String description;

    private UserRoleEnum(Integer code, String description) {
        this.code = code;
        this.description = description;
    }

    public String getDescription(a) {
        return description;
    }

    public int getCode(a) {
        return this.code; }}Copy the code

4.2 Maintaining Roles

Assume that the user already has the common user role, we need to add the administrator role for it, which is the new role, and corresponding to the delete role and query role, these operations need to use the bit operation, please refer to the code comments.

/** * User role enumeration **@authorPublic number JAVA Front * */
public enum UserRoleEnum {

    / / 1 - > 00000001
    NORMAL(1."Ordinary user"),

    / / 2 - > 00000010
    MANAGER(1 << 1."Administrator"),

    / / 4 - > 00000100
    SUPER(1 << 2."Super Administrator");// Add a role -> bit or operation
    // oldRole -> 00000001 -> Common user
    // addRole -> 00000010 -> New administrator
    // newRole -> 00000011 -> Common user and administrator
    public static Integer addRole(Integer oldRole, Integer addRole) {
        return oldRole | addRole;
    }

    // Delete role -> bit XOR operation
    // oldRole -> 00000011 -> Common user and administrator
    DelRole -> 00000010 -> Delete the administrator
    // newRole -> 00000001 -> Common user
    public static Integer removeRole(Integer oldRole, Integer delRole) {
        return oldRole ^ delRole;
    }

    // Whether there is a role -> bits and operations
    // allRole -> 00000011 -> Common user and administrator
    // qryRole -> 00000001 -> Whether there is an administrator role
    // resRole -> 00000001 -> There are common user roles
    public static boolean hasRole(Integer role, Integer queryRole) {
        return queryRole == (role & queryRole);
    }

    private int code;
    private String description;

    private UserRoleEnum(Integer code, String description) {
        this.code = code;
        this.description = description;
    }

    public String getDescription(a) {
        return description;
    }

    public int getCode(a) {
        return this.code;
    }

    public static void main(String[] args) {
        System.out.println(addRole(1.2));
        System.out.println(removeRole(3.1));
        System.out.println(hasRole(3.1)); }}Copy the code

4.3 Data Query

Assume that the user data with common user roles needs to be queried on the operation background query page. The SQL statement is as follows

select * from user_role where (user_flag & 1) = user_flag;
select * from user_role where (user_flag & b'0001') = user_flag;
Copy the code

Use the MyBatis statement as follows

<select id="selectByUserRole" resultMap="BaseResultMap" parameterType="java.util.Map">
  select * from user_role 
  where user_flag & #{userFlag} = #{userFlag}
</select>

<select id="selectByUserIdAndRole" resultMap="BaseResultMap" parameterType="java.util.Map">
  select * from user_role 
  where id = #{userId} and user_flag & #{userFlag} = #{userFlag}
</select>
Copy the code

5 Article Summary

In this paper, we start with a simple case and analyze the advantages and disadvantages of directly adding fields. When using the method of adding fields, the most common problem encountered by the author is that in complex business scenarios, adding field data requires additional docking work, which increases the development cost.

We introduce bitmap method, so that a field can represent multiple meanings, reduce field redundancy, save docking development cost. Of course, bitmap method also has disadvantages, it increases the cost of code understanding, database field meaning is not intuitive, need to escape, you can choose to use according to the needs of the scene.