MySQL Database Design

Database design: The process of planning and structuring the data entities in the database and the relationships between those entities.

Design process:

• Demand analysis: Analyze the customer's business and data processing needs.
• Summary design: Draw the ER diagram of the database to confirm the correctness and completeness of the requirement information.
• Detailed design: Convert ER diagram to multiple tables, carry out logical design, take the primary key foreign key of the human cousin table, and apply the three major paradigms of database design to audit. Finally select a specific database.

Second, the three main paradigms of database design:

(1) First paradigm

Objective: To ensure the atomicity of each column.

If each column is the smallest unit of data (the smallest atomic unit) that cannot be divided, the first normal form is satisfied.

(2) Second paradigm

Goal: Make sure that each column in the table is related to the primary key.

If a database satisfies the first normal form and all columns other than the primary key depend on the primary key, the second normal form is satisfied.

If a primary key has more than one column, the other columns must all have dependencies on the two primary key columns. Otherwise it can be divided into two tables ~

(3) The third paradigm

Goal: Make sure that each column is related to the primary key column, not indirectly.

If a relationship satisfies the second normal form, and the columns other than the primary key can only depend on the primary key, there is no interdependence between the columns and columns, and the third paradigm is met.

The third paradigm is also a constraint on field redundancy, where no fields can be derived from other fields, so there is no redundancy required for the fields.

Duplicate occurrences of primary key and foreign key in multiple tables are not data redundancy, and duplication of non-key fields is data redundancy.

MySQL Database Design