A detailed description of the database three paradigms

The concept of paradigm

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 (ensure that each column remains atomic)

　　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.

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 (ensure that each column in the table is related to the primary key)

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). That is, in a database table, a table can only hold one data, not a variety of data can 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

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:

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 (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 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, you can add fields to the order form about other information about the customer (such as name, company, etc.). The design as shown in the following two tables is a database that satisfies the third paradigm.

In this way, when querying the order information, the customer number can be used to refer to the records in the Customer information table, and the content of the customer information can be entered multiple times in the order Information table, reducing the data redundancy.

Precautions:

1. The essential difference between the second paradigm and the third paradigm is that there are no two sheets.

The second paradigm is to say that a table contains a number of different entities of the attributes, then must be divided into multiple tables, the third paradigm is the request has been divided into multiple tables, a table can only have another table ID, and no other information (any other information will be used in the primary key in another table query).

2, must satisfy the first normal form to satisfy the second normal form, must simultaneously satisfy the first second normal form to satisfy the third normal form.

A detailed description of the database three paradigms