MySQL database paradigm design method

1. Design Paradigm

Question: What is paradigmatic design and why is de-normalized design needed?

Normal From EnglishNormal From. In order to design a good database logical relationship during the development process, certain constraints must be met. This constraint forms a development paradigm, which is divided into several levels, with each level being stricter than the previous level.

Meeting these paradigms can theoretically make our database logical structure more concise and clear.

The following are the four common normal forms:

• First Normal Form (1NF)

• Second Normal Form (2NF)

• Third Normal Form (3NF)

• Fourth Normal Form (BCNF)

1. First normal form (1NF)

• Each column is an attribute value that cannot be subdivided, ensuring the atomicity of each column;

• The attributes of the two columns are close or similar or the same. Try to merge columns with the same attributes to ensure that no redundant data is generated;

• The columns with a single attribute are composed of basic data types;

• The tables designed are all simple two-dimensional tables.

Example: User shipping address Counter example:

Name	Phone	Address
张三	138000000	Beijing-Chaoyang District-Jiuxianqiao Street

Positive example:

##NamePhone numberprovincecity区街张三138000000 -Beijing CityChaoyang DistrictJiuxianqiao Street

Summary: Each column is Atomic values ​​that cannot be subdivided (a column cannot be subdivided, such as mailing address and province, city, district)

2. Second normal form (2NF)

• The second normal form (2NF) is built on the basis of the first normal form.

• Second Normal Form (2NF) requires that the attributes of an entity are completely dependent on being associated with the primary key. The so-called completion dependency refers to the partial attributes that cannot exist and exist depending on the keyword. If they exist, then this attribute and the keyword part should be separated to form a new entity. The new entity has a one-to-many relationship with the original entity.

Counter example:

##Product ID100

User ID	Product name	User name	Purchase quantity	Order time
1	Microwave oven A102	王马子	1	2022-08-08

Positive example:

Order form

Product ID100Product List

User ID	Purchase quantity	Order time
1	1	2022-08-08

Product ID100User table

Product Name
Microwave oven A102

User ID1

User name
王 Mazi

Summary: Eliminate the partial functional dependence of the column on the primary key (for the partial dependence on the composite primary key, For example: product ID user ID is the primary key, and there are some primary key dependent fields such as user name, product name, etc.)

3. Third normal form (3NF)

To satisfy the third normal form (3NF), the second normal form (2NF) must be satisfied.
Third Normal Form (3NF) requires that a data table does not contain non-primary key keyword information that is already included in other tables, that is, the data cannot have a transitive relationship, that is, each attribute must be There is a direct relationship with the primary key rather than an indirect relationship.

Counter example:

##Order IDUser IDProductIDProduct NameProduct Manufacturer11100Microwave oven A102 Midea22200Inverter air conditioner B101Haier Positive example: Order table

Order IDUser IDProduct ID112Product information sheet

##100		2
200

Product IDProduct nameMicrowave oven A102Inverter air conditioner B101

Product manufacturer

100

Midea

200

Haier

Summary: Eliminate the transitive dependence of fields on non-primary keys (that is, you need to cancel redundant information such as product name, product address, etc. in the order).

2. Normalized design

In terms of the definition of real database specifications, it is very rigorous. For example, the definition of second normal form (2NF) "If a certain relationship R term first normal form , and each non-primary attribute is completely functionally dependent on the candidate code, then the relation R belongs to the second normal form."

The best design is not a design that strictly follows standardized theory, but the most suitable design solution that can be continuously practiced and summarized based on specific business scenarios.

3. Anti-standardization design

The so-called anti-standardization design is aimed at standardization. 1. Properly violate the requirements for database paradigm design for the sake of performance and reading efficiency; 3. For the sake of query performance, some (a small amount) of redundant data is allowed to exist. In other words, denormalized design is to directly trade space for time.

• Product information

##IDProduct name Product priceProduct descriptionProduct picture address##1

Microwave oven A101	$100.99	Microwave oven that can heat food	tupian.baidu.com

##Classification Information

Category IDCategory Name1Electrical appliances

Product classification correspondence table

Product IDCategory ID1

	##1

De-standardization design of product information

##ID

Product name Category NameProduct PriceProduct DescriptionProduct Image AddressElectric appliances

				##1	1
$100.99	Microwave oven that can heat food	tupian.baidu.com

The above is the detailed content of MySQL database paradigm design method. For more information, please follow other related articles on the PHP Chinese website!