Mysql database optimization two (database design)

related articles

Mysql database optimization one (storage engine)
Mysql database optimization three (SQL optimization)

Database Design

Three paradigms of database design

First Normal Form 1NF: Field Atomicity

The first paradigm simply means that the fields in the table are the smallest and indivisible. Let's take an example below. We see the following user table. The fields inside are inseparable, so it conforms to the atomicity of the first normal form. Some friends may wonder, this address can also be divided into three fields: province, city, district/county, yes! If it is an e-commerce project that needs to be subdivided, it does not conform to the first paradigm. Therefore, it depends on the specific requirements of the project. There is no fixed standard. In the project requirements, its design can no longer be divided, so it conforms to the first paradigm!

Insert picture description here

The second normal form 2NF: Eliminate partial dependence on the primary key

The use of the second paradigm is based on the premise that the first paradigm needs to be satisfied. Add a business field to the table, and the primary key is not used for business processing. For example, our product table has a product id, and the product id is the primary key of the product, but we need to create one The product number column is used to handle the business. Because the id is too sensitive, we use the product number to handle the business, such as the display number when displaying the product, etc.!

Insert picture description here

The third normal form 3NF: add foreign keys on the basis of 2NF

The use of the third paradigm must meet the second paradigm, requiring that each column in the table is directly related to the primary key, not indirectly, (each column in the table can only depend on the primary key). For example, in the following example, there are customers in the order table For related information, after separating the customer table, there is only one user id (foreign key) in the order table, and no other customer information. Because other customer information is directly related to the user id, not directly related to the order id. As shown in the figure below:

Insert picture description here


After separation:

Insert picture description here
The three paradigms are just the basic concepts of general database design, and a database with less redundancy and a reasonable structure can be established. If there are special circumstances, of course, they must be treated specially. The most important thing in database design is to look at requirements and performance, demand>performance>table structure. So you can't blindly pursue the paradigm to build a database!

In addition, my friends are recommended to take an active part in: C Station Evaluation Challenge