Paradigm of the structure design of the "Normal form" database table

Excellent articles for reference

Database (first paradigm, second paradigm, third paradigm)

Database design is a serious, critical matter, a graduate, to join a large industry project, where senior engineers, there to teach me how the database is critical, sacred, unfathomable concept, so I have been with the eyes of worship.

A few years ago, the project manager handed me the database design work for a small project, which I spent at night and on weekends. Later, more because the first time responsible for the entire system database design, but also consulted the previous company's architecture brother Siang, help me to check, see what wood I have thought of.

Later, the design passed the review, very happy, after all, the first time I design a system of table structure, although, is a small system.

So, how many times have the database table structure design experience, how to describe the principle that your design table follows? At this time, the return to the university textbooks, is the paradigm (normal Form).

First paradigm: atomicity, no further division

Atomicity, that is, the field should be non-re-divided.

Whether it is atomic

For example, if the address of a system is used for mailing goods, the addresses in the following table are in accordance with the first paradigm.

Id	User_name	Age	PHONE	ADDRESS
1	Nick Huang	18	12345678	Shenzhen Luohu District Wang Building, room 1003

And how to judge whether or not atomicity, this needs to be based on the actual business judgment.

For example, some systems based on the address for delivery services, the use of the above structure is to meet the first paradigm, and some systems, in addition to delivery, but also need to the user's geographical distribution to do long-term statistics, for statistical convenience, the above address design does not meet the atomicity, perhaps should be:

Id	User_name	Age	PHONE	Province	City	ADDRESS
1	Nick Huang	10	12345678	Guangdong Province	Shenzhen	Room 1003, ground king Building

Typical example: multiple pieces of information with a delimiter stitching record

There are some other typical non-atomic, which is to put multiple data in a field.

such as the phone field:

Id	User_name	Age	PHONE
1	Nick Huang	10	23658745,25654150

This is also the case, such as the Ext_fields field:

Id	User_name	Age	PHONE	ADDRESS	Ext_fields
1	Nick Huang	10	12345678	Shenzhen xxx	<DATA><JOB>Programmer</JOB><PASSPORT>12345678</PASSPORT></DATA>

Second Paradigm: a non-primary key must be completely dependent on the primary key, not just part of the primary key

A non-primary key must be completely dependent on the primary key, not just part of the primary key (Federated primary key)

Examples that do not conform to this attribute

The "Summary design of Rights management system" has a series of user rights tables, if this is an example, the table structure is designed as follows, it does not conform to the second paradigm:

USER_ID, role_id-key, describes the relationship between the user and the role, and status describes whether the relationship is valid or invalid.

As can be seen, the status is a description of the association, is dependent on user_id, role_id, that is, completely dependent on the primary key.

And user_name is the user name, it relies only on user_id, that is, only part of the primary key. The setting of the User_name field does not conform to the second paradigm.

If one day the user's name needs to be modified, then it is necessary to modify each of the associated data related to this user.

Third paradigm: A non-primary key must be directly dependent on the primary key, rather than passing dependent or indirect dependencies

A non-primary key must be directly dependent on the primary key, rather than passing dependent or indirect dependencies.

Examples that do not conform to this attribute

The "Summary design of Rights management system" has a series of user rights tables, if this is an example, the table structure is designed as follows, it does not conform to the third paradigm:

This is the role table, the ID is the role id,name is the role name, the status is in effect for this role, and system_id the name of the system id,system_name this role belongs to for this role.

You can see that system_name is a transitive dependency, system_id dependencies and IDs in the role table, and System_name has dependencies with system_id. The setting of the System_name field does not conform to the third paradigm.

Something

Is the database design must strictly abide by the 3 paradigm?

This is not necessarily, depending on the situation, in fact, many common situations deliberately set up redundant fields to make system queries more efficient and more convenient.

Paradigm of the structure design of the "Normal form" database table