Database regularization and design techniques

In the design of dynamic website, the importance of database design is self-evident. If the design is improper, the query is very difficult, the performance of the program will be affected. Whether you're using MySQL or an Oracle database, you can make your PHP code more readable and extensible by formalizing the table design, which also improves the performance of your application.

Simply put, normalization is the elimination of redundancy and uncoordinated dependencies when designing tables. In this article, I'll go through five incremental processes to tell you the regularization techniques you should know in your design. Thus, a feasible and efficient database is established. This article also gives a detailed analysis of the types of relationships available.

This assumes that we want to create a table of user information that stores the user's name, company, company address, and some personal favorites or URLs. At the beginning, you might define a table structure as follows:

0 State Form

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 has been done, we refer to this form of table as a table in 0 State form. Notice where the URL1 and URL2 fields---if we need a third URL in the application? So you need to add one more column to the table, which is obviously not a good idea. If you want to create an extensible system, consider using the first formalized form and applying it to the table.

First Level regularization form

1. Eliminate repeated groups in each table

2. Create a separate form for each set of related data

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

The above table clearly violates the first rule above, then what does the third key mean? Quite simply, it simply adds a unique, automatically incremented integer value to each record. With this value, you can distinguish between records with the same two names. By applying the first level normalization form, 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 form of the first level of normalization, it has solved the restrictions of the URL field, but this process has brought a new problem. Every time we insert a record in the user table, we have to repeat all the company and user data. This not only makes the database larger than it was before, but it is prone to error. It is therefore subject to a second level of regularization.

Second level regularization form

1. To create a separate table for fields that apply to more than one record

2. To associate the values of these tables with a foreign key

We put the value of the URL in a separate table so that we can add more data at a later time without having to worry about producing duplicate values. We also associate these fields with primary key values:

Users

UserId Name Company Company_address

1 Joe ABC 1 Work Lane

2 Jill XYZ 1 Job Street

URLs

Urlid Reluserid URL

1 1 abc.com

2 1 xyz.com

3 2 abc.com

4 2 xyz.com

As shown above, we have created a separate table in which the primary key userid in the users table is now associated with the foreign key reluserid in the URL table. It seems that the situation has improved markedly. But what if we have to include an employee record for ABC company? Or more, 200? So we have to reuse the company name and address, which is obviously not redundant. So we will apply the third level regularization method: