The three paradigms of MySQL design

Find some information on the Internet, record the following and add your own understanding.

When designing the relational database, we should design a reasonable relational database to conform to different specifications, and the different norm requirements are called different paradigms, and the higher paradigm database redundancy is lower. But sometimes the pursuit of the paradigm to reduce redundancy, but will reduce the efficiency of data read and write, this time will be anti-paradigm, the use of space to change time.

There are currently six paradigms for relational databases: the first paradigm (1NF), the second paradigm (2NF), the third paradigm (3NF), the bath-cod paradigm (BCNF), the fourth paradigm (4NF) and the fifth paradigm (5NF, also known as the perfect paradigm). The paradigm that satisfies the minimum requirements is the first paradigm (1NF). The second paradigm (2NF) is further satisfied on the basis of the first paradigm, and the rest of the paradigms are referred to by analogy. In general, the database only needs to meet the third normal form (3NF) on the line. So this is just a three-paradigm-related knowledge.

Three paradigms 1NF: field is not divided;
2NF: Primary key, non-primary key field depends on primary key;
3NF: Non-primary key fields cannot depend on each other;

Explain:
1NF: Atomic field can not be re-divided, otherwise it is not a relational database;
2NF: Uniqueness A table shows only one thing;
3NF: Each column is directly related to the primary key, there is no transitive dependency;

First Paradigm (1NF)

That is, the column of the table is atomic, non-decomposition, that is, column information, can not be decomposed, as long as the database is a relational database (Mysql/oracle/db2/informix/sysbase/sql server), it automatically satisfies 1NF. Each column of a database table is an indivisible atomic data item, not a collection, an array, a record, and other non-atomic data items. if an attribute in an entity has multiple values, it must be split into different properties . Popular understanding is that a field stores only one item of information.

Relational database: Mysql/oracle/db2/informix/sysbase/sql server
Non-relational database: (Feature: Object-oriented or set)
NoSQL database: Mongodb/redis (characterized by document-oriented)

Second Paradigm (2NF)

The second paradigm (2NF) is established on the basis of the first paradigm (1NF), i.e. satisfying the second normal form (2NF) must first satisfy the first paradigm (1NF). The second normal form (2NF) requires that each instance or row in a database table must be divided by a unique region. for the implementation of the distinction, we usually need to design a primary key to implement (where the primary key does not contain business logic).

That is to satisfy the first paradigm premise, when there are more than one primary key, it will occur in the case of non-conforming to the second paradigm. For example, there are two primary keys, which cannot exist, and it depends only on one of the primary keys, which is non-conforming to the second normal. Popular understanding is that any one field is dependent on the same field in the table . (related to the splitting of the table)

Third Paradigm (3NF)

Satisfying the third normal form (3NF) must first satisfy the second normal form (2NF). In short, the third paradigm (3NF) requires that a database table not contain non-primary key fields already contained in other tables. That is, the information of the table, if it can be deduced, it should not be a separate design of a field to be stored (as far as possible foreign key join with the foreign key join). Many times, we tend to divide a table into multiple tables in order to satisfy the third paradigm.

That satisfies the second paradigm premise, if a property relies on other non-primary key attributes, and the other non-primary key attributes depend on the primary key, then this property is indirectly dependent on the primary key, which is called passing dependent on the primary attribute. The popular explanation is that a table has a maximum of two layers of the same type information .

Inverse three paradigms

No redundant database is not necessarily the best database, sometimes in order to improve operational efficiency, improve read performance, it is necessary to reduce the paradigm standard, appropriate retention of redundant data. The practice is to adhere to the third paradigm when designing the conceptual data model, and to lower the standard of normalization into the design of the physical data model. Reducing the paradigm is to increase the field, reduce the query when the association, improve the efficiency of the query, because the database in the operation of the proportion of the query is much larger than the ratio of DML. But the anti-normalization must be moderate, and in the original already meet the three normal forms on the basis of adjustment.

Understanding of the above paradigm and inverse paradigm:

Excerpted from https://www.zhihu.com/question/19900437
Copyright belongs to the author.
Commercial reprint please contact the author for authorization, non-commercial reprint please specify the source.
Sun Wenliang
Links: https://www.zhihu.com/question/19900437/answer/14089402
Source: Know


A database design should also be divided into three realms:
The first realm, the beginning of the database design, the importance of the paradigm has not been deeply understood. The anti-paradigm design that appears at this time will generally be problematic.
The second realm, with the problem solving problems, gradually understand the real benefits of the paradigm, so as to quickly design a low-redundancy, high-efficiency database.
The third realm, after the N-year exercise, is bound to find the limitations of the paradigm. At this point, to break the paradigm, design a more reasonable anti-paradigm part.
The paradigm is like the martial arts inside the tricks, the beginner delusion does not follow the tricks, can only die very embarrassed. After all, tricks are the essence of summing up the master summary. And with the improvement of martial arts, tricks, must be found in the limitations of the tricks, either forget the tricks, or self-created tricks. As long as the efforts, coupled with more than a few years, always reach the second realm, always feel that the paradigm is classic. At this time can not be too dependent on the paradigm, rapid breakthrough paradigm limitations of the people, Nature is a master.

The three paradigms of MySQL design