Three paradigms of database design

One 、 Preface

In order to establish less redundancy 、 A well structured database , There are certain rules that must be followed when designing a database . In a relational database, such rules are called paradigms . A paradigm is a summary of a design requirement . To design a relational database with reasonable structure , Must meet certain paradigms .

There are three most common design paradigms in actual development ：

Two 、 First normal form ( Make sure that each column remains atomic )

The first paradigm is the most basic paradigm . If all field values in the database table are atomic values that cannot be decomposed , It shows that the database table satisfies the first paradigm .

The reasonable following of the first paradigm needs to be determined according to the actual needs of the system . For example, some database systems need to use “ Address ” This attribute , Would have been “ Address ” Property is designed as a field of a database table . But if the system often accesses “ Address ” Attribute “ City ” part , Then we must “ Address ” This property is subdivided into provinces 、 City 、 Detailed address and other parts for storage , In this way, it is very convenient to operate on a certain part of the address . Only in this way can the design satisfy the first paradigm of database , As shown in the following table .

The user information shown in the above table follows the requirements of the first paradigm , In this way, it is very convenient for users to use cities for classification , It also improves the performance of the database .

3、 ... and 、 Second normal form ( Make sure that each column in the table is related to the primary key )

The second paradigm goes one step further than the first . The second paradigm needs to ensure that every column in the database table is related to the primary key , You can't just relate to a part of the primary key （ Mainly for the union primary key ）. That is to say, in a database table , Only one kind of data can be saved in a table , It is not allowed to save multiple data in the same database table .

For example, to design an order information table , Because there may be many kinds of goods in the order , Therefore, the order number and the product number should be used as the joint primary key of the database table , As shown in the following table .

Order information sheet

So there's a problem ： In this table, the order number and product number are used as the joint primary key . In this way, the commodity name in the table 、 Company 、 Commodity price and other information are not related to the primary key of this table , It's just about the item number . So it violates the design principle of the second paradigm .

If the order information table is split , Separate product information into another table , Separate the order item table into another table , It's perfect . As shown below .

This design , It greatly reduces the redundancy of the database . If you want to get the product information of the order , Use the commodity number to query the commodity information table .

Four 、 Third normal form ( Make sure that each column is directly related to the primary key column , Not indirectly )

The third paradigm needs to ensure that each column of data in the 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 to establish a corresponding relationship with the order table . You can't add other information about customers to the order form （ Such as name 、 Company, etc ） Field of . The design shown in the following two tables is a database table that meets the third paradigm .

In this way, when querying the order information , You can use the customer number to refer to the records in the customer information table , You don't have to enter the customer information in the order information table many times , Reduced data redundancy .