SoFunction
Updated on 2024-10-30

A Brief Analysis of the Three Paradigms of Database Design

In order to create less redundant and well-structured databases, certain rules have to be followed while designing the database. In relational databases such rules are called paradigms. A paradigm is a summary that meets a certain design requirement. To design a well-structured relational database, certain paradigms must be satisfied.
There are three design paradigms that are most common in real-world development:

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

The first paradigm is the most basic one. If a database table in theAll field values are non-decomposable atomic values, it means that the database table satisfies the first paradigm.

Reasonable adherence to the first paradigm needs to be based on the system'sactual requirementTo determine. For example, some database systems need to use the "address" attribute, the "address" attribute was directly designed as a database table field on the line. However, if the system often accesses the "city" part of the "address" attribute, then it is necessary to re-divide the "address" attribute into multiple parts such as province, city, detailed address, and so on. part of the 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 above table follows the first paradigm, which makes it very easy to categorize the user's use of the city and improves the performance of the database.

2. Second paradigm (ensure that every column in the table is related to the primary key)

The second paradigm builds on the first paradigm. The second paradigm needs to ensure that every column in a database table is related to the primary key and not just a part of the primary key (mainly for joint primary keys).This means that in a database table, only one type of data can be stored in a table, and multiple types of data cannot be stored in the same database table.

For example, to design an order information table, because there may be multiple items in the order, the order number and item number should be used as the joint primary key of the database table, as shown in the following table.

Order Information Form

This creates a problem: the table is using the order number and item number as the joint primary key. So in this table the product name, unit, product price and other information is not related to the primary key of the table, but only related to the product number. So the second paradigm design principle is violated here.

And if you split this order information table, separating the product information into another table, and separating the order items table into another table as well, it would be perfect. As shown below.

This design reduces database redundancy to a large extent. If you want to get the product information of an order, just use the product number to look up in the product 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 the data table in theEach column of data is directly related to the primary key, not indirectly.

For example, when designing an order data table, you can use the customer number as a foreign key to establish a corresponding relationship with the order table. Instead, you can add fields about other information about the customer (e.g., name, company affiliation, etc.) to the order table. The design shown in the following two tables is a database table that satisfies 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, and you do not have to enter the contents of the customer information multiple times in the order information table, reducing data redundancy.

This is an introduction to the three main paradigms of databases, and I hope it will help you to design less redundant and well-structured databases.