Three major database design paradigms and three database paradigms

Three major database design paradigms and three database paradigms

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. The first paradigm (ensuring that each column remains atomic)

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.

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

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, rather than only a part of the primary key (mainly for the joint 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, to design an order information table, because there may be multiple commodities in the order, order numbers and commodity numbers should be used as the joint primary keys of the database table, as shown in the following table.
Order info table

In this case, a problem occurs: This table uses the order number and product number as the joint primary key. In this way, the product name, unit, and price information in the table are not related to the primary key of the table, but only to the product number. This violates the design principles of the second paradigm.
However, if we split the order information table, split the product information into another table, and split the order project table into another table, it would be perfect. As shown below.

This design greatly reduces the database redundancy. If you want to obtain the product information of an order, use the product number to query the product information table.

3. Third Paradigm (ensure that each column is directly related to the primary key column, rather than indirectly related, that is, there cannot be dependency)

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 and company) fields in the order table. As shown in the following two tables, the design is a database table that meets the third paradigm.

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.

Summary:

1 paradigm: 1NF is an atomic constraint on attributes. Attributes must be atomic and cannot be decomposed;
In general, can fields be further divided? If it is too late, it is in line with the 1NF design.

Second paradigm: 2NF is the uniqueness constraint on the record and requires that the record have a unique identifier, that is, the uniqueness of the object;
For example, if you create a table with a girl appointment, you do not need to record her height and weight in each appointment record. You can simply store the height and weight in a table for query.

Third paradigm: 3NF is a constraint on field redundancy, that is, any field cannot be derived from other fields, and it requires that the fields are not redundant.
For example, if you put the user ID and user profile pictures in the comment table, it is not appropriate. The user profile picture depends on the user ID instead of the comment.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.