Database Normalization and design skills (1)

Source: Internet
Author: User

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
Name company company_address url1 url2
Joe ABC 1 Work Lane
Jill XYZ 1 Job Street
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:
UserId name company company_address url
1 Joe ABC 1 Work Lane
1 Joe ABC 1 Work Lane
2 Jill XYZ 1 Job Street
2 Jill XYZ 1 Job Street
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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.