[Understand in a single minute] Take you to thoroughly understand the paradigm and anti-paradigm database design

Preface

We often hear about paradigm design and anti-paradigm design, so when should we follow paradigm design? When should he violate his rules? With these two questions, let me take you to study now~

Paradigm

The paradigm is given by the predecessors through continuous verification. In order to establish a database with less redundancy and a reasonable structure, it is a certain rule that must be followed in the design of the database. In a relational database, this specification is called a paradigm. This article not only explains the design of the paradigm, but also Some examples will be given, and the data table design given by the analysis with you will belong to the first paradigm.

1NF First Normal Form

Rules to follow: each column of attributes is an indivisible attribute value to ensure the atomicity of each column

is no longer separable? For example, user name, gender, and age contact information can no longer be divided.
But for example, the city, Tongzhou District, Beijing, we can still subdivide a field into provinces: Beijing District: Tongzhou District

First, we see that in the user information table, the city where it is located can be subdivided.

coding	username	age	gender	Contact information	City
1	Zhang San	18	male	1888888888	Qingdao, Shandong
2	Li Si	17	Female	1999999999	Wuhan, Hubei
3	Wang Wu	22	male	1777777777	Qingdao, Shandong

In the following table, we divide the city into two fields, province and city, according to the above user information table. After this design, the data table meets the requirements of the first normal form.

User code	username	age	gender	Contact information	Province	City
1	Zhang San	18	male	1888888888	Shandong Province	Qingdao City
2	Li Si	17	Female	1999999999	Hubei Province	Wuhan
3	Wang Wu	22	male	1777777777	Shandong Province	Qingdao City

---

Let's make an explanation with the product list

coding	product name	price	in stock	Manufacturing plant
1	iPhone 13 white	1999	100	Foxconn Taiwan
2	iPhone 12 black	1299	20	Beijing Foxconn
3	iPhone 13 red	2239	30	Shanghai Inventec

Still in accordance with the above rules, first we find the attributes that can no longer be divided

Now the indivisible attributes include code, price (only RMB), inventory, and the name of the product and the manufacturing plant must be known to everyone. This is an attribute that can be subdivided, then look at the data table that meets the first normal form below. design

coding	brand	model	colour	price	in stock	Manufacturing plant (location city)	Manufacturing plant name
1	iPhone	13	White	1999	100	Taiwan	Foxconn
2	iPhone	12	black	1299	20	Beijing	Foxconn
3	iPhone	13	Red	2239	30	Shanghai	Inventec

We split the product name from the manufacturing plant as follows to achieve the indivisible attribute

relationship mode:

Commodity name (brand, model, color)
Manufacturing plant (city where manufacturing plant is located, name of manufacturing plant)

---

Seeing this, you must have understood the design rules of the first paradigm, so let's continue the study of the second paradigm.

2NF Second Normal Form

Rules to follow: First, it must meet the requirements of the first normal form. Secondly, only one type of data can be stored in a table. Make sure that each column is related to the primary key. We use the course code as the primary key. Other information except the course name has nothing to do with the primary key, so We only need independent course codes and course names, and create a new table for the others.

Course Code	Course Title	Instructor	Teacher sex	Teacher contact
10001	C++ programming	Zhang San	male	1888888888
10002	data structure	Li Si	Female	1999999999
10003	operating system	Wang Wu	male	1777777777

Course Schedule

Course Code	Course Title
10001	C++ programming
10002	data structure
10003	operating system

teacher table

Course Code	Instructor	Teacher sex	Teacher contact
10001	Zhang San	male	1888888888
10002	Li Si	Female	1999999999
10003	Wang Wu	male	1777777777

After this split, we meet the requirements of the second normal form, and also meet the requirements of the first normal form, and there is no subdivision attribute.

---

Let's take the above-mentioned commodity table that conforms to the first normal form as an example

coding	brand	model	colour	price	in stock	Manufacturing plant (location city)	Manufacturing plant name
1	iPhone	13	White	1999	100	Taiwan	Foxconn
2	iPhone	12	black	1299	20	Beijing	Foxconn
3	iPhone	13	Red	2239	30	Shanghai	Inventec

Here, code and brand are used as the combined primary key, and a new table is created for attributes that are not related to the primary key.

Manufacturer table

coding	Manufacturing plant (location city)	Manufacturing plant name
1	Taiwan	Foxconn
2	Beijing	Foxconn
3	Shanghai	Inventec

Commodity table

coding	brand	model	colour	price	in stock	Manufacturer code
1	iPhone	13	White	1999	100	1
2	iPhone	12	black	1299	20	2
3	iPhone	13	Red	2239	30	3

This meets the requirements of the second normal form.

We already have a general understanding of the second paradigm, don’t stop, the third paradigm is waiting for you!

3NF Third Normal Form

Rules to follow: First meet the requirements of the second paradigm, and there can be no transitive dependencies among other columns except the primary key column.

Let me first explain what is transitive dependency. Take the following order form as an example. We can find the product name through the order code, but we can also find the shipping warehouse and contact information through the store code. Both order code -> shipping warehouse, store code -> shipping warehouse, this will generate transitive dependency. The popular point is that if there are no non-primary keys related to attributes (shop code|sales code) except the primary key (order code) Warehouse), we can only find all the information through the primary key, to achieve the exclusive situation, which means that the next SQL statement can not make it valid, then the requirements of the third normal form have been met.

SELECT 发货仓,店铺电话 FROM 订单表 WHERE 商铺编码 = S1

Order code	product name	Store code	Shipping warehouse	Shop phone
NS001	Xiaomi mobile phone	S1	A shipping warehouse in North China	1888888888
NS002	Samsung handphone	S2	A cat's delivery warehouse in East China	1999999999
NS003	Huawei cell phone	S3	A certain Huaxi shipping warehouse	1777777777

Meet the requirements of the third normal form according to the following split

Order Form

Order code	product name	Store code
NS001	Xiaomi mobile phone	S1
NS002	Samsung handphone	S2
NS003	Huawei cell phone	S3

shop table

Store code	Shipping warehouse	Shop phone
S1	A shipping warehouse in North China	1888888888
S2	A cat's delivery warehouse in East China	1999999999
S3	A certain Huaxi shipping warehouse	1777777777

Just mentioned that the third normal form must meet the second normal form, and the first normal form must meet the first normal form, then let’s take a look at the order form that has just been dismantled.

1. There are no more detachable attributes, which conforms to the first paradigm.
2. There is no attribute irrelevant to the primary key, which conforms to the second paradigm.
3. There is no transitive dependency and conforms to the third normal form.

Therefore, when you meet the third paradigm, you actually meet the first and second paradigms. Then we will end the three paradigms of relational databases. There may be some ambiguities in the data. Please understand the principles and don’t entangle them. thanks.

The purpose of anti-paradigm design

Of course, the paradigm is only the proposed specification, but in some special cases, we have to consider more points, such as

1. Concurrent reading.
2. Reduce the number of table associations.
3. Redundant design of data sheet.
4. Hot data and cold data.
5. The non-real-time nature of the data.

Here is a comparison with the three major paradigms (personal opinion)

• Reduce the number of table associations = violates the second normal form
• Redundant design of data table = violation of third normal form

Of course, it is not a generalization. Here we take the order form as the guide to explain why we have to violate the rules of the paradigm.

---

Make a question here

After the user places an order, if the basic information of the product is associated with the table, what will happen if the product code of the order table is associated with the code of the product table?

Probably there will be such a problem:

After the user bought an HP printer for 1999 yuan, the merchant modified the current product and changed it to an ASUS notebook. Then through the table association (set to conform to the paradigm rule), will the user go crazy?

So how to solve this problem?

This uses the redundant design of the data I proposed above, and stores the basic information of the product directly in the order form. This is based on common sense. I will go to the mall and buy a watermelon for 20 yuan. The next day this Regardless of whether the price of watermelon increases or decreases, the mall will not ask you to make up the difference, and you cannot ask the mall to refund the difference (of course, this has nothing to do with the purchase of an expensive package, don’t compete).

So in the age of the Internet, at the moment you place an order, the information about the products you buy is already fixed, whether the merchant modifies the product, deletes the product, or the merchant shuts down and closes the store. The goods you buy will always be seen in your order. Of course, if the goods expire, after you click to enter, a treasure may give you a snapshot, which is still the data saved when you place the order.

You should be able to understand why anti-paradigm is needed. There are many similar examples. I have put forward similar requirements many times in my e-commerce design-related articles. So far, I will not list them one by one.

Of course, under normal circumstances, you still need to design your paradigm based on three main business, which is the industry norm, only a standardized uniform, you will not too "pit" You pick pot class people.

---

So what are the advantages of anti-paradigm design?

1. Improve query speed.
2. Avoid multi-table queries.
3. Reduce the pressure on the database.

Problems that should be paid attention to in anti-paradigm

1. Too much redundant data leads to excessive storage.
2. If necessary, it needs to be implemented with a memory database.
3. Violation of the paradigm rules, insert exceptions, query exceptions, etc. will occur.

Thanks

Thank you for seeing here, I hope this article can help you, thank you.