Database Normalization and design skills (1)

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.