Do you know the three major paradigms of database design?

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. 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, when designing an order data table, it cannot contain other data unrelated to the order information. The design of the following table completely violates the design theory of the second paradigm.

Order info table

 

Order No.	ORDER item	Owner	Salesman	Order Quantity	CUSTOMER NAME	Product Name	Product Price
001	Excavator	Liu Ming	Li Dongming	1 unit	Li Cong	Optical Control Switch	12 ￥
002	Impact Drill	Li Gang	Huo Xinfeng	Eight	Liu xinming	Speed Regulating Motor	230 $
003	Shovel Truck	Guo Xin	Aimei	2 Vehicles	Li Cong	Chemical Pump	809 $

 

From the above table, although the last two columns are not meaningless data, they have nothing to do with the "Order Number" of the primary key column. Therefore, the second paradigm assumes that this design is unreasonable.

 

3. Third paradigm

The third paradigm goes further on the basis of the second 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 or company to 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	Shovel Truck	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.

Link: http://www.cnblogs.com/zhhh/archive/2011/04/21/2023355.html