Optimization of data tables in MySQL database, analysis of foreign keys and usage of three paradigms

This article uses examples to illustrate the optimization of data tables in MySQL databases, the use of foreign keys and three paradigms. Share with you for your reference, the details are as follows:

Data table optimization

Optimize the product information table

1. Create a product category table:

create table if not exists goods_cates( 
id int unsigned primary key auto_increment, 
name varchar(40) not null 
);

2. Write the product category into the product category table:

Note: You do not need to add values ​​when inserting query results into another table

insert into goods_cates(name) 
(select cate_name from goods group by cate_name); 

3. Update the product category table to the product table:

Connect the product table to the category table, and use the category table id to replace the product table category content

update (goods_cates inner join goods on goods_cates.name=goods.cate_name) 
set goods.cate_name=goods_cates.id;

4. Modify the table structure:

Rename the product category name field cate_name in the product table to cate_id

alter table goods change cate_name cate_id int unsigned not null;

Foreign Keys

1. Use of foreign keys:

Add a foreign key constraint and insert the data line to determine whether cate_id exists in the id in goods_cates. If not, the data insertion fails.

alter table table name 1 add foreign key (field name 1) references table name 2 (field name 2); 

(Setting a foreign key can prevent illegal data entry)

foreign key (field name 1) references table name 2 (field name 2); 

(You can also set the foreign key directly when creating the table, provided that the table to be connected exists)

2. Disadvantages of foreign keys:

Although foreign keys can effectively prevent the entry of illegal data, they will greatly reduce the update efficiency of the table. Therefore, in actual development, foreign key constraints are generally not applied, and the legitimacy of the data can be judged at the transaction level.

3. Deletion of foreign keys:

Display the statement to create the table

show create table goods; 

Delete the foreign key after getting its name:

alter table goods drop foreign key goods_ibfk_1;

Three Paradigms

1. First Normal Form (1NF): emphasizes the atomicity of columns, that is, columns cannot be divided into other columns.
2. Second Normal Form (2NF): Based on 1NF, the table must have a primary key, and non-primary key fields must be completely dependent on the primary key, rather than only part of the primary key.
3. Third Normal Form (3NF): Based on 2NF, non-primary key columns must be directly dependent on the primary key, and there cannot be transitive dependencies.

A database that conforms to the third normal form is a more reasonable database.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.