Database normalization and design tips _ MySQL

In the design of dynamic websites, the importance of database design is self-evident. If the design is not proper, the query will be very difficult and the program performance will be affected. Whether you are using a mySQL or Oracle database, regular table design can make your PHP code more readable, easier to expand, and thus improve application performance. To put it simply, in the design of dynamic websites, the importance of database design is self-evident. If the design is not proper, the query will be very difficult and the program performance will be affected. Whether you are using a mySQL or Oracle database, regular table design can make your PHP code more readable, easier to expand, and thus improve application performance.

To put it simply, normalization is to eliminate redundancy and uncoordinated subordination during table design. In this article, I will show you the normalization skills you should understand in design through five gradual processes. To establish a feasible and efficient database. This article also analyzes in detail the available link types.

Assume that we want to create a user information table, which stores the user name, company, company address, and personal favorites or URLs. At the beginning, you may define the following table structure:

Zero status

Users

Name company company_address url1 url2

Joe ABC 1 Work Lane abc.com xyz.com

Jill XYZ 1 Job Street abc.com xyz.com

Since no normalization is performed, we call this form of table as a zero-state table. Pay attention to the url1 and url2 fields. what if we need a third url in the application? In this way, you need to add a column to the table. Obviously, this is not a good solution. If you want to create a scalable system, you need to consider using the first formal form and apply it to the table.

Level 1 normalization

1. remove duplicate groups in each table

2. create an independent table for each set of related data

3. use a primary key to identify each set of related data

The preceding table clearly violates the first rule. what does the primary key of the third statement mean? Simply put, it adds a unique, automatically increasing integer value to each record. With this value, two records with the same name can be distinguished. Through the first-level normalization form of the application, we get the following table:

Users

UserId name company company_address url

1 Joe ABC 1 Work Lane abc.com

1 Joe ABC 1 Work Lane xyz.com

2 Jill XYZ 1 Job Street abc.com

2 Jill XYZ 1 Job Street xyz.com

Now our table can be said to be in the first-level Normalization Form. it has solved the restriction problem of url fields, but this processing has brought about a new problem. Every time we insert a record in the user table, we must repeat all the company and user data. This not only makes the database bigger than before, but also is prone to errors. Therefore, it must undergo second-level regularization.