First chicken or egg: mutual dependency in the database

This dish encountered such a problem when designing the database:

The database has two tables, group table and member table. The group table has a creator field and the member table has a group field.

 

 

Looking at the logical design, it raises a philosophical question: how can we get a chicken first? The two tables form mutual dependencies. When the database was just built, there was no data in both tables, so inserting data into any table would fail.

The problem is solved immediately, so I searched the internet and found the following sentence: "If the two tables are correlated, the relationship is many-to-many. According to the third paradigm, create a third intermediate table to store the primary keys of the two tables. The fields of the third table are used for association. ". According to this rule, two intermediate tables are created to store the primary keys of the group table and the primary keys of the member table (the other table is opposite), and then the two intermediate tables are associated, this can perfectly solve this problem.

 

 

Because the two intermediate tables describe the direct dependency between group tables and Member tables, they are converted to the dependency between the two intermediate tables on group tables and Member tables. This looks good, but after careful observation, we will find that there is not a many-to-many relationship in this problem: a group has only one Creator, and one member can only belong to one group.

Although many-to-many solutions can be used, this is not the root cause of the problem. So where is the problem? Imagine that if we want to create a group, we must create a group by a person. The level of the person is higher than that of the group, and the current designer and group are at a peer-to-peer level, this is obviously unreasonable. This is because all the Members are put in a table at the time of design, regardless of common users, operators, and administrators. As a result, the Administrator's hierarchy is reduced and the actual situation is analyzed, the Administrator should not belong to a group.

 

 

To solve this problem, roles with management functions must be separated from common roles. management roles must be at the group level and have no group permissions, but have the permission to create a group; the level of a common role is under the Group. It must belong to a group and has no permission to create a group. In this way, the problem is solved in a more practical way. It must be noted that the adminmember table and the normalmember table only represent the permission classification domain table and do not represent the Member type. For example, the adminmember table can contain user types such as super administrator, administrator, and operator, in the normalmember table, user types such as group members, group leaders, and group leaders are allowed. In the memberall table, the typeid field should be included to identify the member type. Therefore, when designing a database, it is necessary to analyze the table hierarchy and divide a specific domain to avoid the error of mutual dependency.

Even so, I personally feel that this design is not very reasonable. We can see that if we delete a user in the adminmember table domain, the Group created by this user will also have to be deleted due to foreign key constraints. This is obviously unreasonable in actual application, which will lead to system chaos.

 

 

Therefore, although the adminmember table and the group table have a foreign key constraint in theory, this constraint cannot be applied to the actual database design. We just want to record the group creator, further, we provide some references without strict integrity requirements. If the Creator does not exist, the group should still exist.