Database design Tips (i)

Skills | design | data | database | Database design 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. No
You are using MySQL or Oracle database, through the formalization of the table design, you can make your PHP code more readable, easier to expand, thereby
will also improve the performance of the application.

Simply put, normalization is the elimination of redundancy and uncoordinated dependencies when designing tables. In this article, I'll go through five progressive processes to tell
The regularization techniques you should be aware of in your design. Thus establishing a workable and
A highly efficient database. 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. In the Open
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
Do you need a third URL in your application? So you need to add one more column to the table, which is obviously not a good idea. If you want to create a rich and extensible
System, you should consider using the first formalized form and apply it to the form.

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? It's simple, it just adds one to each record.
A unique, automatically incremented integer value. With this value, you can distinguish between records with the same two names. By applying the first level normalization form, we
The following table was obtained:

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 problem of URL field restrictions, 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 more
Before the big, and very easy to make mistakes. It is therefore subject to a second level of regularization.