Database three normal forms and anti-normal forms

In the daily development process, we need to operate the Mysql database, which involves the construction of tables. How to create a well-structured database table requires following the following three database paradigms.

1. The first normal form

ensures the atomicity of each column (field) in the data table.

If each field in the data table is the smallest data unit that cannot be further divided, it satisfies the first normal form.

For example: user table, containing fields id, username, password

2. The second normal form

is in the second normal form Going one step further on the basis of normal form, the goal is to ensure that every column in the table is related to the primary key.

If a relationship satisfies the first normal form and all other columns except the primary key depend on the primary key, it satisfies the second normal form.

For example: a user has only one role, and one role corresponds to multiple users. Then you can establish the data table relationship as follows to make it meet the second normal form.

User user table, fields id, username, password, role_id

role role table, fields id, name

The user table is associated with the role table through the role id (role_id)

3. The third normal form

is a step further based on the second normal form. The goal is to ensure that the columns in the table are directly related to the primary key. Relevant, not indirect.

For example: one user can correspond to multiple roles, and one role can also correspond to multiple users. Then you can establish the data table relationship as follows to make it meet the third normal form.

user user table, fields id, username, password

role role table, field id, name

user_role user-role intermediate table, id ,user_id,role_id

Like this, the relationship between the user table and the role table is established through the third table (intermediate table), and at the same time it conforms to the principle of normalization, it can be called the third normal form.

4. Denormalization

Denormalization refers to improving the read performance of the database by adding redundant or duplicate data.

For example: Add the field role_name to the user_role user-role intermediate table in the above example.

Denormalization can reduce the number of join tables during related queries.

The above is the table creation paradigm for MYSQL database that I have compiled. I hope it will be helpful to everyone in the future.

Related articles:
MySQL index optimization covering index

Mysql field type selection method

The connection between mysql locks and indexes

The above is the detailed content of Database three normal forms and anti-normal forms. For more information, please follow other related articles on the PHP Chinese website!