Knowledge of design paradigm of database

In order to build a database with small redundancy and reasonable structure, we must follow certain rules when designing database. In a relational database, this rule is called a paradigm. A paradigm is a summary of a design requirement. In order to design a relational database with reasonable structure, a certain paradigm must be met.

The most common design paradigms in real-world development are three:

1 . First Paradigm

The first paradigm is the most basic paradigm. If all the field values in a database table are non-exploded atomic values, the database table satisfies the first paradigm.

The rational follow-up of the first paradigm needs to be determined according to the actual needs of the system. For example, some database systems need to use the "address" attribute, the "address" attribute should be directly designed as a database table field. However, if the system often accesses the "city" part of the "address" attribute, then it is not to be the "address" attribute to be re-split into provinces, cities, detailed address and other parts of storage, so that in the address of a part of the operation will be very convenient. This design satisfies the first paradigm of the database, as shown in the following table.

User Information table

number	name	sex	age	contact phone	province	City	detail address
1	Zhang Hongxin	Man	26	0378-23459876	Henan	Kaifeng	No. 23rd Xinhualu, Chaoyang District
2	Li Shiping	Woman	32	0751-65432584	Guangzhou	Guangdong	No. 148th, Tianming Road, Baiyun District
3	H	Man	21st	0371-87659852	Henan	Zhengzhou	No. 198th Erqi District University Road
4	Guo Xiaoming	Woman	27	0371-62556789	Henan	Zhengzhou	No. No. 218, Xinzheng bei Jie, Xue Dian

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

2 . Second Paradigm

The second paradigm is based on the first paradigm in a more advanced layer. The second paradigm needs to ensure that each column in a database table is related to the primary key, not just one part of the primary key (primarily for the Federated primary key). In other words, in a database table, only one data can be saved in a table, and multiple data cannot be saved in the same database table.

For example, to design an order information table, because there may be more than one item in the order, the order number and the product number are used as the federated primary key for the database table, as shown in the following table.

order Information Form

Order number	Product number	Product Name	Number	Unit	Price	Customer	Affiliated units	Contact information
001	1	Excavator	1	Console	1200000￥	Tom	On Hai Shi	020-1234567
001	2	Impact Drills	8	Put	230￥	Tom	On Hai Shi	020-1234567
002	3	Forklift	2	Car	980000￥	John doe	Beijing Company	010-1234567

This creates a problem: The table is the Union primary key with the order number and the product number. In this table, the product name, unit, commodity price and other information is not related to the table's primary key, but only related to the product number. So this violates the design principle of the second paradigm.

And if the Order Information table is split, the product information is separated into another table, the Order Item table is also separated into another table, it is perfect. as shown below.

order Information Form

Order number	Customer	Affiliated units	Contact information
001	Tom	On Hai Shi	020-1234567
002	John doe	Beijing Company	010-1234567

Order Item table

Order number	Product number	Number
001	1	1
001	2	8
002	3	2

Product Information Sheet

strong> units

Impact Drill

valign= width= "Top" and "$" > TD valign= "Top" width= "ten" >

Product Code	Product name	product price
1	Excavator	Table /td>	1200000 ￥
2		230 ￥
	Forklift	Cars	980000 ￥

This design, to a large extent, reduces the redundancy of the database. If you want to get the product information for an order, use the item number to inquire in the product information sheet.

3 . Third Paradigm

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

For example, when designing an order data table, the customer number can be used as a foreign key and order table to establish the corresponding relationship. Instead of adding fields to the order form about other customer information (such as name, company, etc.). The design shown in the following two tables is a database table that satisfies the third paradigm.

Order Information Form

Order number	Order Items	Head	Salesman	Order Quantity	Customer number
001	Excavator	Liu ming	Partner	1 units	1
002	Impact Drills	Li Gang	Hownie	8 x	2
003	Forklift	Guo Niu	Amelie	2 Cars	1

Customer Information Form

customer number	customer Name	owned company	Contact Information
1	Lee Cong	51 construction	13253661015
2	Liu Xinmin	self-employed	13285746958

In this way, when the order information is queried, the customer number can be used to refer to the records in the Customer information table, and the data redundancy is reduced without having to enter the contents of the customer information multiple times in the Order Information table.

Knowledge of design paradigm of database