Database regularization and design skills
QQ New Human, Yesky
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 establish a feasible and highly high-efficiency 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 name company company company_address url1 url2 joe abc 1 Work lane abc.com xyz.com jill xyz 1 job street abc.com xyz.com Due to no regular Treatment, 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. The first level of normalized 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 the table of each associated data to clearly violate the provisions of the first article above, what is the main 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 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 form can be said in the form of a first-level normalization. It has solved the limitations of the URL field, but this process has brought a new problem. Every time you 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.
The second level of normalized form 1. Establish a separate table 2 for the fields of multiple records. Values associated with these tables via a Foreign Key We put the value of the URL in a separate table so that we can add more data in the future without worrying about generating a duplicate value. We also associate these fields through primary key: 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 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 repeatedly use the company name and address, which is pronounced enough to reduce redundancy. So we will apply the third level of normalization: the third level of normalization form 1. Eliminating Fields, which does not rely on this key, have no relationship with the USER ID, so they apply their own company ID: Uses 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 This way we will associate with the primary key COMID in the Companies table. 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 notice 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 relationships before defining the fourth formal form, I want to first mention three basic data relationships: one-on-one, one-to-many 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 discussion, let's take a look at the table structure. 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_RELATIONS RELATIONID RelatedURLID RelationUserid 1 1 2 1 2 3 2 1 4 2 In order to further reduce data redundancy, we use the fourth-level regular 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 a specific requirement of the fourth formalized form: the fourth formal form 1. In a multi-to-many relationship, the independent entity cannot be stored in the same table because it is only applied to many-to-many relationships, so 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, the following will select all JoE-based URLs: SELECT NAME, URL from users, URLS, URLATEDUSERID = 1 and users.RLID = 1 And Urls.urlid = URL_RLATION.RELATEDURLID If we want to traverse everyone's personal information and URL information, we can do this: SELECT NAME, URL from Users, URLs, URL_RELATION WHERE USERS.USERID = URL_RLATION.RELATEDUSERID AND URLS.URLID = URL_RELATIONS.RELATEDURLID The fifth level of normalization has a formal form of normalization, it is not common, a bit deeply, and it is unnecessary in most cases. Its principles are: 1. The original form must be able to re-build this rule by the form separated by it is that you can make sure that you do not introduce extra columns in the separated form, all the table structure you created is as big as their actual needs. . Applying this provision is a good habit, but unless you have to handle a very large data, you will not need to use it.
