Database design skills (three) ------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------ author:? allsky ?? defined in the fourth regularized Before the form, I would like 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? Say, 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 what changes will change the table structure. Userid? Name? Relcompid? 1? Joe? 1? 2? Jill? 2? Companies? CompiT? Company? Company_ADDRESS? 1? ABC? 1? WORK? Lane? 2? Xyz? 1? Job? Street? Urls? Urlid? Url? 1? Abc.com? 2? Xyz.com? URL_RELATIONS? RELATID? RELATEDURLID? RelationUserid? 1? 1? 1? 2? 1? 2? 3? 2? 1? 4? 2? 2? In order to further reduce the redundancy of data, we use the fourth-level normalization form. We created a quite strange URL_RELATIONS table, all the fields of 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: the fourth formalized 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. Therefore, 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,? URLS,? URL_RLATIONSSWHERESURL_RLS.RLATEDUSERESURL_RLS.RLATEDUSERID? =? 1 • And? Users.userid? =? 1? And? Urls.urlid? =? Url_relevants.relatedURLID? If we want to traverse everyone's personal information and URL information, we can do this: SELECT? Name,? Url? From • Users,? urls,? url_relationsswheresusers.userid? =? url_reralid? =? url_reletions.relateduserid? and? urls.urlid? =? URL_RLS.RLID? =? URL_RLATIONS.RELATEDURLID? The fifth-level formal form is also a formal form of normalization, it is not common, a little deep And in most cases it is unnecessary.
