MySQL Series Database Design Three Paradigm Tutorial Examples

1. Knowledge description of the three paradigms of database design

1. What is a design paradigm?

The basis for designing tables is that tables designed according to these three paradigms will not have data redundancy.

2. Why should we learn the three database paradigms?

The database design paradigm is the specifications that the database design needs to meet. A database that meets these specifications is concise and has a clear structure. At the same time, no exceptions will occur in insert, delete, and update operations. On the contrary, it will be a mess, which will not only cause trouble for database programmers, but also be ugly and may store a lot of unnecessary redundant information.

Although we data analysts do not need to design the database ourselves, understanding the three database paradigms can be helpful for our understanding of the tables later.

3. What are the three paradigms?

1) First normal form: Any table should have a primary key, and each field should be atomic and indivisible.

2) Second normal form: It is based on the first normal form and requires that all non-primary key fields are completely dependent on the primary key and no partial dependency can occur.

Solution: Many-to-many, three tables, and two foreign keys in the relationship table.

3) Third normal form: built on the basis of the second normal form, all non-primary key fields directly depend on the primary key and cannot generate transitive dependencies.

Solution: One-to-many, two tables, add foreign keys to the multiple tables.

Note: Keep in mind the two sentences in blue above.

Reminder: In actual development, the main focus is on meeting customer needs, and sometimes redundancy is traded for execution speed.

2. Classic design scheme of database table

How to design one-to-one?

The first solution: primary key sharing

The second solution: foreign key unique

Note: The unique foreign key situation is a bit like the many-to-many situation described above. When the foreign key field is added with a
After the unique restriction, it becomes one-to-one.

The above is the detailed content of the MySQL series of database design three paradigm tutorial examples. For more information about the MySQL database design three paradigms, please pay attention to other related articles on 123WORDPRESS.COM!