Three paradigms of database design

Summed up is: The first paradigm is not split
The second is total reliance on
Third elimination of transitive dependencies
Three paradigms of database design

In order to build a database with less redundancy and reasonable structure, some rules must be followed in designing the database. In a relational database, this rule is called a paradigm. A paradigm is a summary that conforms to a particular design requirement. In order to design a relational database with reasonable structure, a certain paradigm must be satisfied.

The most common design paradigm in actual development is three:

1. First paradigm (ensure that each column remains atomic)

The first paradigm is the most basic paradigm. If all the field values in a database table are atomic values that are not decomposable, the database table satisfies the first normal form.

The rational compliance of the first paradigm needs to be determined according to the actual requirements of the system. For example, some database systems need to use the "address" this attribute, the "address" attribute directly to the design of a database table fields on the line. However, if the system often accesses the "City" section of the "address" attribute, then the "address" attribute must be split into provinces, cities, detailed addresses, and so on, so that it is convenient to operate on one part of the address. This design only satisfies the first normal form of the database, as shown in the following table.

The user information shown in the table above follows the requirements of the first paradigm, so it is convenient to classify users using the city and improve the performance of the database.

2. Second paradigm (make sure that each column in the table is related to the primary key)

The second paradigm is more advanced on the basis of the first paradigm. Second paradigm needs to ensure that each column in a database table is related to a primary key, not just a part of a primary key (primarily for a federated primary key). This means that 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 a variety of items in the order, the order number and the item number are used as the joint primary keys for the database table, as shown in the following table.

Order Information Table

This creates a problem: This table is based on the order number and the item number as the joint primary key. In this table, the product name, unit, commodity price and other information are not related to the table's primary key, but only related to the commodity number. So it violates the design principle of the second paradigm.

And if you split the order Information table, separate the product information into another table, and separate the Order Items table into another table, it's perfect. as shown below.

This design, to a large extent, reduces the redundancy of the database. If you want to obtain the product information of the order, use the item number to inquire in the commodity information table.

3. Third paradigm (ensure 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 datasheet is directly related to the primary key, not indirectly.

For example, when designing an order datasheet, you can establish a relationship between the customer number as a foreign key and the order table. Instead of adding fields to the order table for other customer information, such as name, company, and so on. The design shown in the following two tables is a database table that satisfies the third normal form.

This way, when you query order information, you can use the customer number to reference the records in the Customer information table, and do not have to enter the contents of the customer information multiple times in the order Information table to reduce the data redundancy.