Three Paradigms of MySQL Design

Posted Jun 4, 20204 min read

Three paradigms of database design

There are three specifications based on the specifications when designing database tables:

The first paradigm:the primary key is required, and the atomicity of each field is indivisible
The second paradigm:all non-primary key fields are required to completely depend on the primary key, and cannot be partially dependent

The third paradigm:all non-primary key fields and primary key fields must not have transitive dependencies

First Normal Form

No duplicate records can appear in the database table, each field is atomic and cannot be divided

Examples that do not meet the first normal form:

![]( https://i0.wp.com/segmentfault.com//upload-images.jianshu.io/upload_images/4807654-eab3b56930c4f552.PNG?imageMogr2/auto-orient/strip%7CimageView2/2/w/587 /format/webp)

First Normal Form 1. PNG

There are problems:

The last record and the first repeat(not unique, no primary key)
The contact information field can be subdivided, not atomic

![]( https://i0.wp.com/segmentfault.com//upload-images.jianshu.io/upload_images/4807654-03cb8c62dabb930c.PNG?imageMogr2/auto-orient/strip%7CimageView2/2/w/585 /format/webp)

First Normal Form 2.PNG

Regarding the first normal form, each row must be unique, that is, each table must have a primary key. This is the most basic requirement for database design. It is mainly represented by a numeric or fixed-length string. The columns cannot be divided, and should be based on specific circumstances. To decide. For example, contact information may use a field for development convenience.

Second Normal Form

The second normal form is based on the first normal form. In addition, all non-primary key fields are completely dependent on the primary key, and some dependencies cannot be generated.

Examples:

![]( https://i0.wp.com/segmentfault.com//upload-images.jianshu.io/upload_images/4807654-b922f9c1d1814859.PNG?imageMogr2/auto-orient/strip%7CimageView2/2/w/586 /format/webp)

2.PNG

Determine the primary key:

![]( https://i0.wp.com/segmentfault.com//upload-images.jianshu.io/upload_images/4807654-b94e4f8adcc2f9cd.PNG?imageMogr2/auto-orient/strip%7CimageView2/2/w/586 /format/webp)

2.PNG

Although the primary key is determined above, there will be a lot of redundancy in this table. The redundant fields involved are "student name" and "teacher name". The reason for the redundancy is that the student name part depends on a field of the primary key The student number does not depend on the teacher number, and the teacher name part depends on the teacher number in a field of the primary key, which is partly dependent on the second normal form.

solve:

![]( https://i0.wp.com/segmentfault.com//upload-images.jianshu.io/upload_images/4807654-ef1c439e3cd7e13b.PNG?imageMogr2/auto-orient/strip%7CimageView2/2/w/580 /format/webp)

Solve .PNG

If a table is a single primary key, then it is a composite second normal form, and part of the dependency is related to the primary key

The above is a typical "many to many" design

Third Normal Form

Based on the second normal form, non-primary key fields cannot be transitively dependent on the primary key field(do not produce transitive dependencies)

![]( https://i0.wp.com/segmentfault.com//upload-images.jianshu.io/upload_images/4807654-c906d1f0cf0c4227.PNG?imageMogr2/auto-orient/strip%7CimageView2/2/w/586 /format/webp)

Three 1.PNG

In the above table, the class name field is redundant, because the class name field does not directly depend on the primary key, the class name field depends on the class number, and the class number depends on the student number. This is transitive dependence, and the solution is to separate the redundant fields. Take it out and build the table:

![]( https://i0.wp.com/segmentfault.com//upload-images.jianshu.io/upload_images/4807654-a6b47b46a606682f.PNG?imageMogr2/auto-orient/strip%7CimageView2/2/w/593 /format/webp)

Solution 3. PNG

The above design is a typical one-to-many design, one is stored in a table, and the other is stored in a table, and the foreign key is added to the side of the one in the many tables.

Several classic designs:

One to one:

The first option:store in two tables and share the primary key
The second scheme:store in two tables, the foreign key is unique

One-to-many:

Store in two tables, add foreign keys in the more side,
This foreign key field refers to the primary key field in one of the parties

Many to many:

Store in three tables, store student information in the student table, and store course information in the course table,
Store the relationship between students and courses in the student elective table

![]( https://i0.wp.com/segmentfault.com//upload-images.jianshu.io/upload_images/4807654-85473b90ab1feee7.PNG?imageMogr2/auto-orient/strip%7CimageView2/2/w/528 /format/webp)

Shared primary key.PNG

![]( https://i0.wp.com/segmentfault.com//upload-images.jianshu.io/upload_images/4807654-13ec5e1d12a119b0.PNG?imageMogr2/auto-orient/strip%7CimageView2/2/w/323 /format/webp)

Foreign key only.PNG

In actual development, the database design follows the three paradigms as much as possible, but it is still based on the actual situation, and sometimes it may be traded for redundancy. The ultimate goal is to meet customer needs