Detailed explanation of paradigm in Mysql

Definition of paradigm

Normal Format: Normal Format is a kind of knowledge in discrete mathematics, which is used to solve the problem of data storage and optimization: after saving the storage of data, it can usually The data found through relationships will not be stored repeatedly. The ultimate goal is to reduce data redundancy.

Paradigm background:

1. To solve data redundancy

2. To optimize and store data

3. After saving the data, the data that can be obtained through the relationship will no longer be stored.

Concept of paradigm:

1. It is a specification of a hierarchical structure, divided into 6 layers

2. Each layer is stricter than the previous layer

3. To satisfy the paradigm of the next layer, the premise is to satisfy the previous layer

4. Paradigm has guiding significance in the database, but it is not mandatory.

5. The database must not only consider space issues, but also efficiency issues. Generally, the first three layers

6 and six-layer paradigms are used , 1NF is the lowest level with the lowest requirements; 6NF is the highest level and the most strict.

Currently there are six paradigms in relational databases: first normal form (1NF), second normal form (2NF), and third normal form (3NF) , Bascord Normal Form (BCNF), Fourth Normal Form (4NF) and Fifth Normal Form (5NF, also known as Perfect Normal Form). Generally speaking, the database only needs to meet the third normal form (3NF).

First normal form: Ensure that each column cannot be further divided

Requirements

1. When designing a table to store data, if the designed fields in the table The stored data requires additional processing (splitting) before being taken out and used, so the design of the table does not meet the first normal form

2. The first normal form requires fields to be atomic and non-divisible

For example:

Lecturer Substitute Table

There is no problem with the design of the above table: But if the requirement is to find out the data, it is required to display a When does the teacher start the class and when does the class end:, you need to split the substitute teaching time: it does not comply with 1NF, the data is not atomic, and can be split again.

Solution

Solution: Split the substitute teaching time into two fields to solve the problem.

Second Normal Form: Ensure that each column in the table is related to the primary key

Requirements

1. During the data table design process, if there is a composite primary key (multi-field primary key), and the fields in the table are not determined by the entire primary key, but depend on Due to a certain field in the primary key (part of the primary key)

2. There is a problem that the field depends on the part of the primary key, which is called partial dependence

3. The second paradigm is to solve the problem of table design. Partial dependencies are allowed

Lecturer’s class schedule

Composite primary key: Lecturer class

In the above table: Substitute time, start and end fields They are all related to the current primary key (lecturer and class), but gender does not depend on class, classroom does not depend on lecturer, gender only depends on lecturer, classroom only depends on class, and gender and classroom depend on part of the primary key: partial dependence. No In line with the second paradigm.

Solution

Solution 1: Gender and lecturer can be separated into separate tables, and classes and classrooms can also be formed into separate tables.

Solution 2: Cancel the composite primary key and use logical primary key

Logical primary key: has no business meaning, such as auto-increment primary key

Business primary key: has business meaning, such as the person table uses the ID number as the primary key

Third normal form: Ensure that each column is directly related to the primary key column, rather than indirectly related

Requirements

1. To satisfy the third normal form, you must satisfy the second normal form

2. Theoretically, all fields in a table should directly depend on the primary key. If there is a field in the table design, it does not directly depend on the primary key. Instead, it relies on a certain non-key field to finally achieve dependence on the primary key. This kind of dependency that does not directly depend on the primary key, but relies on non-primary key fields is called transitive dependency

3. The third paradigm is Solve the problem of transitive dependency

Lecturer brings class schedule

Primary key: ID

In the above design scheme: Gender-dependent lecturer exists, lecturer-dependent Primary key; Classroom depends on class, and class depends on primary key: Both gender and classroom have transitive dependencies.

Solution

Take out the fields with transitive dependencies and the dependent fields themselves to form a separate table, and then when the corresponding information is needed, use the primary key of the corresponding entity table to add it. #

Classroom table

Anti-paradigm

Sometimes, when designing a table Sometimes, if there are several fields in a table that need to obtain information from another table, theoretically speaking, the desired data can indeed be obtained, but the efficiency is lower, and other tables will be deliberately not saved in some tables. Instead of directly saving the desired data information, one table can directly provide data when querying data, without the need for multi-table queries ((low efficiency), but redundancy will increase

Recommended mysql video tutorial, address: https://www.php.cn/course/list/51.html

The above is the detailed content of Detailed explanation of paradigm in Mysql. For more information, please follow other related articles on the PHP Chinese website!