Three common patterns of easy-to-understand Database Design

To establish Redundancy

To establish Redundancy

In order to establish a database with low redundancy and reasonable structure, certain rules must be followed when designing the database. In relational databases, such a rule is called a paradigm. The paradigm is a summary that meets a certain design requirement. To design a rational relational database, a certain paradigm must be met.

There are three most common design paradigms in actual development:

1. First paradigm

The first paradigm is the most basic paradigm. If all the field values in the database table are unrecoverable Atomic values, it indicates that the database table meets the first paradigm.

The rational compliance of the first paradigm needs to be determined based on the actual needs of the system. For example, the "Address" attribute must be used in some database systems. You can directly design the "Address" attribute as a field in a database table. However, if the system frequently accesses the "city" section in the "Address" attribute, therefore, you have to re-split the "Address" attribute into provinces, cities, detailed addresses, and other parts for storage. This makes it very convenient to operate a part of the address. This design satisfies the first paradigm of the database, as shown in the following table .?

User information table

No.	Name	Gender	Age	Contact number	Province	City	Detailed address
1	Zhang hongxin	Male	26	0378-23459876	Henan	Kaifeng	23 Xinhua Road, Chaoyang District
2	Li Siping	Female	32	0751-65432584	Guangzhou	Guangdong	No. 148, tianming Road, Baiyun District
3	Liu Zhiguo	Male	21	0371-87659852	Henan	Zhengzhou	No. 198, University Road, Erqi District
4	Guo Xiaoming	Female	27	0371-62556789	Henan	Zhengzhou	No. 218, xuedian North Street, Xinzheng City

The user information shown in the table follows the requirements of the first paradigm, which makes it very convenient to classify users using cities and improves the database performance.

2. Second paradigm

The second paradigm goes further on the basis of the first paradigm. The second paradigm needs to ensure that each column in the database table is related to the primary key, rather than only a part of the primary key (mainly for the joint primary key ). That is to say, in a database table, only one type of data can be saved in one table, and multiple types of data cannot be saved in the same database table.

For example, to design an order information table, because there may be multiple commodities in the order, order numbers and commodity numbers should be used as the joint primary keys of the database table, as shown in the following table.

Order info table

Order No.	Product NO.	Product Name	Quantity	Unit	Price	Customer	Unit	Contact info
001	1	Excavator	1	Platform	1200000 $	Zhang San	Shanghai Xiaozhi	020-1234567
001	2	Impact Drill	8	Set	230 $	Zhang San	Shanghai Xiaozhi	020-1234567
002	3	Forklift	2	Vehicle	980000 $	Li Si	Beijing Company	010-1234567

In this case, a problem occurs: This table uses the order number and product number as the joint primary key. In this way, the product name, unit, and price information in the table are not related to the primary key of the table, but only to the product number. This violates the design principles of the second paradigm.

However, if we split the order information table, split the product information into another table, and split the order project table into another table, it would be perfect. As shown below.

Order info table

Order No.	Customer	Unit	Contact info
001	Zhang San	Shanghai Xiaozhi	020-1234567
002	Li Si	Beijing Company	010-1234567

Order project table

Order No.	Product NO.	Quantity
001	1	1
001	2	8
002	3	2

Item info table

Product NO.	Product Name	Unit	Product Price
1	Excavator	Platform	1200000 $
2	Impact Drill	Items	230 $
3	Forklift	Vehicle	980000 $

This design greatly reduces the database redundancy. If you want to obtain the product information of an order, use the product number to query the product information table.

3. Third paradigm

The third paradigm needs to ensure that each column of data in the data table is directly related to the primary key, but not indirectly related.

For example, when designing an order data table, you can use the customer number as a foreign key and order table to establish a corresponding relationship. You cannot add other customer information (such as name and company) fields in the order table. As shown in the following two tables, the design is a database table that meets the third paradigm.

Order info table

Order No.	ORDER item	Owner	Salesman	Order Quantity	Customer ID
001	Excavator	Liu Ming	Li Dongming	1 unit	1
002	Impact Drill	Li Gang	Huo Xinfeng	Eight	2
003	Forklift	Guo Xin	Aimei	2 Vehicles	1

Customer Info table

Customer ID	CUSTOMER NAME	Company	Contact info
1	Li Cong	51 Construction	13253661015
2	Liu xinming	Individual business	13285746958

In this way, when querying order information, you can use the customer number to reference records in the customer information table. You do not have to enter the customer information multiple times in the order information table, which reduces data redundancy.