In the design of a dynamic website, the importance of database design is self-evident. If the design is improper, it is very difficult to query, and the performance of the program will also be affected. Whether you are using a MySQL or Oracle database, you can make your PHP code more readable and easier to expand by performing normalized table design, which will also improve the performance of the application.
Simply put, normalization is to eliminate redundancy and uncoordinated dependence when designing. In this article, I will tell you the formalization skills you should understand in the design through five progressive processes. Thereby, a feasible and highly efficient database. This article also analyzes the type of relationship that can be utilized.
Here, we have to create a table of user information, where the user's name, company address, and some personal favorites or URLs. At the beginning, you may define a table structure:
Zero-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 there is no normalization process, we will refer to the form of a form of a zero-state form. Pay attention to the URL1 and URL2 fields --- If we need the third URL in the app? This way you have to add more columns in the form, it is obvious, this is not a good way. If you want to create an extensibility system, you should consider using the first normalized form and apply it to the table.
First level formalized form
1. Eliminate the repeated group 2 in each table. Establish a separate form 3 for each associated data. Use a primary key to identify each set of related data
The above forms have obviously violate the provisions of the article above, then what does the primary key of the third article? Very simple, it is just adding a unique and automatic increase in each record. With this value, you can separate the two names of the same name. By applying the first level of normalization, we get the following form:
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 form can say that it is already in the form of the first level, it has solved the limitations of the URL field, but this process has brought a new problem. Every time I insert a record in the USER table, we must repeat all companies and user data. This not only makes the database than before, and it is easy to make mistakes. Therefore, it is also necessary to process the second level. Second levels of normalization form
1. Establish a separate table 2 for the fields of multiple records. Cover the value of these forms through a Foreign Key
We put the value of the URL in a separate form so that we can add more data in the future without worrying about the value of repetition. We also associate these fields by primary key:
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 created a separate table, the primary key userid in the UserS table is now associated with the Foreign Key Reluserid in the URL table. The current situation seems to have been significantly improved. However, if we have to join an employee record for ABC? Or more, 200? This way we must repeat the company name and address, which is obviously not redundant. So we will apply the third level of normalization: the third level of normalization
1. Eliminate fields that do not depend on this key
The company's name and address is not related to the User ID, so they apply their own company ID:
Userid Name Relcompid 1 Joe 1 2 Jill 2
Companies Compi Company Company_Address 1 ABC 1 Work Lane 2 XYZ 1 Job Street
URLS URLID RELUSERID URL 1 1 ABC.COM 2 1 xyz.com 3 2 ABC.com 4 2 xyz.com
In this way we will associate with the primary key COMID in the Companies table to the Relcompid's Foreign Key, even if you join 200 employees for ABC, there is only one record in Companies. Our Users and URLS tables can be expanded, without having to worry about inserting unnecessary data. Most developers believe that after three steps are enough, this database is designed to be very convenient to handle the burden of the entire enterprise, which is correct in most cases.
We can pay attention to the field of the URL - - Do you pay attention to the redundancy of the data? If you enter the HTML page of these URL data to the user user is a text box, if you can enter, this is not a problem, the two users enter the same favorite probability, but if it is through a drop-down menu, only Let users choose two URL inputs, or more. In this case, our database can also perform the next level of optimization - fourth step, for most developers, this step is ignored, because it relies on a very special relationship - one Multi-to-many relationships, this is not met in our application. Data relationship
Before defining the fourth formal form, I want to first mention three basic data relationships: one-on-one, one more and more. Let's look back at the first normalized Users table. If we put the field of the URL in a separate table, each time I insert a record in the Users table, we will insert a line in the URLS table. We will get a one-on-one relationship: each line in the user table will find the corresponding line in the URLS table. For our application, this is neither practical.
Then look at the second normalized example. For each user record, our table allows multiple URLs to be associated with it. This is a one-to-many relationship, this is a very common relationship.
For many-to-many relationships, it is a bit complicated. In the example of our third formal form, one of our users is related to many URLs, and we want to change the structure to allow multiple users to be related to multiple URLs so we can get a multi-pair Multi-structure. Before discussing, let's take a look at what the table structure changes.
Userid Name Relcompid 1 Joe 1 2 Jill 2
Companies Compi Company Company_Address 1 ABC 1 Work Lane 2 XYZ 1 Job Street
URLS URLID URL 1 ABC.COM 2 XYZ.com
URL_RELATION RELATIONID RelatedURLID RelationUserid 1 1 1 2 1 2 3 2 1 4 2 2 In order to further reduce data redundancy, we use the fourth-level normalized form. We created a quite strange URL_RELATIONS table, which is the primary key or Foreign Key. With this table, we can eliminate repetition items in the URLS table. The following is the specific requirements of the fourth formal form:
Fourth formal form
1. In a multi-to-many relationship, the independent entity cannot be stored in the same table.
Since it is applied only to many-to-many relationships, most developers can ignore this provision. However, in some cases, it is very practical. This example is that we have improved the URLS table by separating the same entity and moved to their own form.
In order to make it easier to understand, we will give a specific example, and the following will use a SQL statement to select all JoE-based URLs:
Select Name, URL from Users, URLS, URL_RLATION WHERE URL_RLATION.RELATEDUSERID = 1 and users.Userid = 1 and Urls.urlid = URL_RLATION.RLATEDURLID
If we want to traverse everyone's personal information and URL information, we can do this:
Select Name, Url from Users, Urls, URL_RLATION where users.Userid = URL_RLATION.RLITEDUSERID AND URLS.URLID = URL_RLATION.RLATEDURLID
Fifth level formalization form
There is also a formal form of normalization, it is not common, a bit deeply, and it is unnecessary in most cases. Its principle is:
1. The original form must be reconstructed by the form separated by it.
The benefits of using this provision are that you can make sure that you don't introduce extra columns in the separated form, all the table structure you created is as big as they actually needed. Applying this provision is a good habit, but unless you have to handle a very large data, you will not need to use it.
I hope this article is useful to you, and you can help you apply these regularizations in all projects. You may want to know where these methods are from, I can tell you that the three normalization regulations are 1972, Dr. Ef Codd is proposed in his paper "further formalized database", the rest The provisions are the theory of later collection theory and relationship mathematicians. Comment: The so-called substance must be reversed, and the table is too fine. Sometimes it is not good, because it is necessary to associate various tables, which will make the query complicated, and efficiency may also decrease, these normalized It can be found in the actual application, and some tests can be made according to the size of the project, and some tests can be made to design a more reasonable table structure.
