Database Design Skills (2) -------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------ author: allsky 1. established for the application in the field of multiple records Separate Table 2. Value to associate these tables through 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 duplicate values. 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: third-level normalization form 1. Eliminate fields that do not rely on this key company name and address are not related to the USER ID, so their apps have their own company ID: Users Userid Name Relcompid 1 Joe 1 2 Jill 2 Companies CompID 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 so we Assign the primary key COMID in the Companies table to the reelcompid's Foreign Key, even if you join 200 employees for ABC, there is only one record in Company. 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.
to be continued..............
