In order to build a database with less redundancy and reasonable structure, certain rules must be followed when designing the database. In a relational database, this rule is called a paradigm. A paradigm is a summary of what meets the requirements of a design. To design a reasonably structured relational database, it is necessary to satisfy certain paradigms.
There are three design paradigms that are most common in actual development:
1. First Normal form (ensuring atomicity for each column)
The first paradigm is the most basic paradigm. A database table satisfies the first normal form if all field values in the table are non-decomposable atomic values.
Proper adherence to the first paradigm depends on the actual requirements of the system. For example, some database systems need to use the “address” attribute, originally directly designed “address” attribute into a database table field on the line. However, if the system often accesses the “city” part of the “address” attribute, it is necessary to re-split the “address” attribute into multiple parts such as province, city, and detailed address for storage, so that it will be very convenient to operate on a part of the address. This design satisfies the first paradigm of databases, as shown in the following table.
The user information shown in the above table follows the requirements of the First paradigm, which makes it very convenient to classify the cities used by users and improves the performance of the database.
2. Second normal form (make sure that every column in the table is related to the primary key)
The second normal form is a step up from the first. The second normal form requires that every column in a database table is associated with a primary key, not just a part of the primary key (primarily for union primary keys). That is to say, in a database table, a table can only save one kind of data, can not save multiple data in the same database table.
For example, if you want to design an order information table, because there may be multiple items in the order, use the order number and item number as the joint primary key of the database table, as shown in the following table.
Order information sheet
This creates a problem: the order number and item number are the joint primary keys in the table. In this way, the commodity name, unit, price and other information in the table are not related to the primary key of the table, but only to the commodity number. So this is a violation of the second normal form of design.
If you split the order information table, split the goods information into another table, and split the order items into another table, it would be perfect. As shown below.
In this way, the redundancy of database is reduced to a great extent. If you want to obtain the product information of the order, use the product number to query the product information table.
3. Third normal Form (make sure that each column is directly related to the primary key column, not indirectly)
The third normal form requires that each column 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 relationship with the order table. It is not possible to add fields to the order form for other information about the customer, such as name, company affiliation, etc. The design shown in the following two tables is a third normal form compliant database table.
In this way, when querying the order information, the customer number can be used to reference the records in the customer information table, and there is no need to enter the customer information in the order information table for many times, reducing the data redundancy.
Finally, the permission role table ER diagram:
