Form writing habits together ！ This is my participation 「 Nuggets day new plan · 4 Yuegengwen challenge 」 Of the 21 God , Click to see the event details .

Design paradigm

problem ： What is paradigm design , Why do we need de normalized design ？

The paradigm comes from English Normal From . In the development process, we should design a good database logical relationship , Certain constraints must be met , This constraint forms the development paradigm , Divided into several levels , One level is stricter than the other .

Meeting these paradigms can theoretically make our database logical structure more concise 、 Clear . Here are four common paradigms ：

• First normal form （1NF）
• Second normal form （2NF）
• Third normal form （3NF）
• Fourth normal form （BCNF）

First normal form （1NF）

1. Each column is a non separable attribute value , Make sure that each column is atomic ;
2. The properties of the two columns are similar or similar or the same , Try to merge columns with the same properties , Make sure that no redundant data is generated ;
3. The columns of a single attribute are composed of basic data types ;
4. The designed tables are simple two-dimensional tables .

give an example ： Customer receiving address Counter example ：

full name	Telephone	Address
Zhang San	138000000	The Beijing municipal - Chaoyang District - Jiuxianqiao Street

Example ：

full name	Telephone	province	City	District	The street
Zhang San	138000000	-	The Beijing municipal	Chaoyang District	Jiuxianqiao Street

summary ： Each column is a non separable atomic value （ A column cannot be subdivided , Such as mailing address and province 、 City 、 District ）

Second normal form （2NF）

1. Second normal form （2NF） It is based on the first paradigm .
2. Second normal form （2NF） The attributes of the entity are required to be completely dependent on the primary key Association . The so-called completion dependency refers to the non existence and existence of some attributes of dependent keywords , If so, the attribute and keyword parts should be separated to form a new entity , The relationship between the new entity and the original entity is one to many .

Counter example ：

product ID	user ID	The product name	User name	Purchase quantity	Order time
100	1	The microwave oven A102	Wang Ma Zi	1	2022-08-08

Example ： The order sheet

product ID	user ID	Purchase quantity	Order time
100	1	1	2022-08-08

Product list

product ID	The product name
100	The microwave oven A102

User table

user ID	User name
1	Wang Ma Zi

summary ： Eliminate partial functional dependence of columns on primary keys （ Partial dependency on combined primary keys , such as ： product ID + user ID Primary key , User name exists , Some primary key dependent fields such as product name ）

Third normal form （3NF）

1. Meet the third paradigm （3NF） The second paradigm must be satisfied （2NF）.
2. Third normal form （3NF） It is required that a data table does not contain non primary key keyword information already contained in other tables , That is, there can be no transfer relationship between data , That is, each attribute has a direct relationship with the primary key instead of an indirect relationship .

Counter example ：

Order ID	user ID	product ID	The product name	Product manufacturer
1	1	100	The microwave oven A102	beauty
2	2	200	Inverter air conditioner B101	haier

Example ： The order sheet

Order ID	user ID	product ID
1	1	100
2	2	200

Commodity information table

product ID	The product name	Product manufacturer
100	The microwave oven A102	beauty
200	Inverter air conditioner B101	haier

summary ： Eliminate the transfer dependency of fields on non primary keys （ It is necessary to cancel the order, such as the product name 、 Redundant information such as product address ）.

Paradigm design

In the definition of real database specification , Very rigorous , For example, the second paradigm （2NF） The definition of “ If a relationship R The first paradigm of terminology , And each non primary attribute completely depends on the candidate code , Relationship R It belongs to the second paradigm ”.

Conclusion ： It is not to say that a design that fully conforms to the standardization theory is the most perfect design , Instead, it depends on the specific business scenario, repeatedly practice and summarize the most appropriate design .

Anti normative design

The so-called denormalization design , It is for Standardization . 1、 Appropriately violate the requirements of database paradigm design for performance and reading efficiency ; 3、 For query performance , Partial... Is allowed （ A few ） Redundant data . let me put it another way , Denormalization design is to exchange space for time directly .

• Commodity information

ID	Name of commodity	commodity price	Commodity Description	Product image address
1	The microwave oven A101	$100.99	Microwave oven that can heat food	tupian.baidu.com

• Categorization information

classification ID	Category name
1	Electrical appliances

• Commodity classification correspondence table

goods ID	classification ID
1	1

• Anti standardized design of commodity information

ID	Name of commodity	Category name	commodity price	Commodity Description	Product image address
1	1	Electrical appliances	$100.99	Microwave oven that can heat food	tupian.baidu.com

Design summary

1. Planning paradigm design of database , In terms of logical structure, it can make the structure more fine-grained , Easy to understand .
2. But in the actual development process , Performance and time costs need to be considered , Often more or less , Will allow data redundancy （ Anti normative design ）, It is usually possible to achieve 2NF.