The pros and cons of Mysql paradigm and anti-paradigm

The pros and cons of Mysql paradigm and anti-paradigm

1. Three major paradigms

First Normal Form

1NF is the atomicity of attributes, which requires attributes to be atomic and cannot be decomposed;

The first normal form is the most basic paradigm. If all field values ​​in the database table are non-decomposable atomic values, it means that the database table satisfies the first normal form. Each column of a database table is an indivisible atomic data item, and cannot be a collection, array, record and other non-atomic data items. In short, first normal form is a domain without repetition.

Second Normal Form

2NF is the uniqueness of the record, which requires the record to have a unique identifier, that is, the uniqueness of the entity, that is, there is no partial dependence;

To satisfy the second normal form, you must first satisfy the first normal form. The second normal form needs to ensure that each column in the database table is related to the primary key, not only to a certain part of the primary key (mainly for joint primary keys). That is to say, in a database table, only one type of data can be saved in a table, and multiple types of data cannot be saved in the same database table.

Third Normal Form

3NF is the redundancy of fields. It requires that any field cannot be derived from other fields. It requires that the field has no redundancy, that is, it does not exist. Transitive dependency;

First of all, it is 2NF. In addition, non-primary key columns must directly depend on the primary key, and there cannot be transitive dependencies. That is, it cannot exist: non-primary key column A depends on non-primary key column B, and non-primary key column B depends on the primary key. In short, Third Normal Form (3NF) requires that a relationship does not contain non-primary key information that is already contained in other relationships. For example, there is a department information table, in which each department has department number (dept_id), department name, department profile and other information. Then after the department number is listed in the employee information table, department name, department profile and other department-related information cannot be added to the employee information table.

Pros and cons of paradigm:

Advantages: Paradigm can avoid data redundancy, reduce database space, and reduce the trouble of maintaining data integrity.

Disadvantages: Tables are designed according to the specifications of the paradigm. The higher the level of the paradigm, the more tables are designed. For example, the table that may be designed in the first normal paradigm may only be one table. When the table is designed in the second normal paradigm, there may be two or more tables. If the table is designed in the third normal paradigm or higher paradigm, Designing this table will result in more tables than in second normal form. The more tables there are, when we query some data, we must query data in multiple tables, so the query time is much higher than the time spent querying in one table. In other words, the higher the paradigm we use, the lower the performance of data operations. Therefore, when we use paradigms to design tables, we must weigh whether to use a higher paradigm to design tables based on specific needs.

Recommended "mysql video tutorial"

2. Anti-paradigm

As the name suggests, it is exactly the opposite of what the paradigm requires. In the anti-paradigm design mode, we can allow appropriate data redundancy and use this redundancy to shorten the time of operating data. That is to say, space is exchanged for time, and data is redundant in multiple tables. When querying, the correlation between tables can be reduced or avoided.

The pros and cons of anti-paradigm:

Advantages: It can reduce the association of tables when querying; it can better perform index optimization;

Disadvantages: There is data redundancy and data maintenance anomalies; modification of data requires more costs;

The above is the detailed content of The pros and cons of Mysql paradigm and anti-paradigm. For more information, please follow other related articles on the PHP Chinese website!