Shulou(Shulou.com)06/01 Report--

Recently, I was responsible for the database table structure design of the company's unified user center. In the process of work, I gave birth to some alternative ideas. I am looking forward to sharing them with you.

The evolution of thought is as follows.

Stage 1:

Enterprises, institutions, families and other entities are abstracted as "organizations", Organization, referred to as "O", allowing multi-level organizations to be associated through parentId;

Subordinate entities of departments and other organizations are abstracted as "departments". Department (abbreviated as "D") allows multi-level departments to be associated through parentId;

Abstract the natural person entity as "natural person", Human, abbreviated as "H";

Abstract "account", Account, abbreviated as "A";

Abstract "role", Role, abbreviated as "R".

Each organization is allowed to have multiple departments; each organization is allowed to have multiple natural persons; and each department is allowed to have multiple natural persons.

Each organization, department, or natural person is allowed to have multiple accounts.

Accounts can share multiple roles.

The E-R relationship in the first stage is shown in Figure 1.

This is a very simple and clear model.

Unfortunately, the real world isn't that simple.

Second stage:

Through in-depth understanding of the product business, the requirements changed to:

An organization has multiple departments, but at the same time a department can belong to multiple organizations. (Example: Two independent drugstore chains share the same warehouse department.)

A department has multiple natural persons, but a natural person can belong to multiple departments. (Example: General manager of a company, part-time department manager of several important departments.)

An organization has multiple natural persons, but a natural person can belong to multiple organizations. (Example: A doctor can practice more than one point.)

The E-R relationship of the second stage is shown in the figure

It was still considered normal.

Third stage:

Add a simple function-postal delivery address.

This is a simple feature, but the complication this time is: Business, does the organization own the shipping address? Does the department have a shipping address? Natural persons have shipping addresses? Or does the account have a shipping address? Is there a one-to-one or one-to-many relationship between entities and shipping addresses? "These questions, the product can not determine the future direction of functional expansion, can only answer" all possible." Haha...

Designed according to the conventional method, add many-to-many relationship tables for organizations, departments, natural persons, accounts, and shipping addresses. The number of relational tables began to swell, as in an orchard where there were more weeds than fruits and vegetables.

In order to reduce the number of weeds, an abstract weed was designed:

Add the relationship between entity (organization, department, natural person, account) and postal address. The real entity is determined by enumerating the two values of entity code and entity id; the receiving address is determined by postal receiving address Id.

For example:

Entity enumeration code O, entity id 1, postal receiving address id 10, indicating that an organization with id 1 has a receiving address with id 10.

Entity enumeration code H, entity id 2, postal receiving address id 20, indicating that: a natural person with id 2 has a receiving address with id 20.

Entity enumeration code A, entity id 3, postal receiving address id 30, indicating: an account with id 3 has a receiving address with id 30.

The third stage E-R relationship is shown in Figure

Stage 4:

At this point the company executives join the demand discussion,

A. The control granularity of the data should reach a single data, and it may be necessary to control the authority according to the relationship between the data and any surrounding entity, but the specific requirements are uncertain, and the maximum elastic design is required.

B. Additional functions such as qualification management and validity period management are added. The business also does not know where the future expansion direction is, so maximum flexibility design is required.

And so the number of abstract weeds began to swell.

For requirement A, OneId table is designed, and IDs of all entities are backed up in the table. Once data row-level permission control is performed in the future, relationship extension is performed through OneId table.

In order to keep the number of abstract weeds from expanding, a universal relational table was designed, as follows:

Universal Relationship Table Entity Enumeration code Entity Id Entity Enumeration code Entity Id

example

Universal Relationship Table Entity enumeration code Entity Id Entity enumeration code Entity IdO1H10O1H11H10A100H10A101

The above data describe

"An organization with Id 1 has two natural persons with id 10 and id 11";

"A natural person with Id 10 has two accounts with id 100 and id 101."

Any complex/undetermined many-to-many relationship can be described using this universal table.

The final version of the entire database design changes to:

Note: No matter how the business expands in the future and how the relationship changes, only the new entity needs to be expanded, and the relationship with other entities does not need to be considered. There will be no impact on the historical design.

This is a departure from all existing database design paradigm, simple and versatile database design method, Universal DB Design Method, UDDM, Judah method for short, feel very appropriate.

For the above design methods, my team has not enough ability to judge the right and wrong, I look forward to everyone actively clap bricks and explore together.

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.