Database Design Skills 3

Before defining the fourth normalization form, I would like to mention three basic data relationships: one-to-one, one-to-many, and multiple-to-many. Let's look back at the story
The first normalized users table. If we put the url field in an independent table, every time we insert a record in the users table, we will
Insert a row into the table. We will get a one-to-one relationship: each row in the User table will find the corresponding row in the urls table. For our applications
This is neither practical nor standard.
Then let's take a look at the second normalization example. For each user record, our table allows multiple urls records to be associated with it. This is a one-to-many
Link. This is a common link.
The multi-to-many relationship is a bit complicated. In our third normalization form example, one of our users is related to many URLs, while
We want to change this structure to allow multiple users to be related to multiple urls, so that we can get a multi-to-many structure. Before the discussion, let's look at the table first.
What will happen to the lattice structure?
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 url
1 abc.com
2 xyz.com
Url_relations
RelationId relatedUrlId relatedUserId
1 1 1
2 1 2
3 2 1
4 2 2
To further reduce data redundancy, we use level 4 normalization. We have created a strange url_relations table with all the fields in it
Is the primary key or foreign key. Through this table, we can eliminate duplicate items in the urls table. The following are the specific requirements for the fourth normalization form:
Fourth normalization form
1. In a many-to-many relationship, an independent object cannot be stored in the same table.

Because it applies only to many-to-many relationships, most developers can ignore this rule. But in some cases, it is very practical.
In this example, the urls table is improved by separating the same entities and moving the relationships to their own tables.
To make it easier for you to understand, we will use an SQL statement to select all urls belonging to joe:
SELECT name, url FROM users, urls, url_relationsswheresurl_relations.relatedUserId = 1 AND
Users. userId = 1 AND urls. urlId = url_relations.relatedUrlId
If we want to traverse the personal information and url information of each person, we can do this:
SELECT name, url FROM users, urls, url_relationsswheresusers.userId = url_relations.relatedUserId AND
Urls. urlId = url_relations.relatedUrlId
Level 5 Regularization
There is also a form of first-level regularization, which is not common and profound, and is unnecessary in most cases. The principle is:
1. The original table must be able to be rebuilt through the table separated by it
The advantage of using this rule is that you can ensure that no extra columns are introduced in the separated table. All the table structures you create are consistent with their actual needs.
Big. Applying this rule is a good habit, but unless you want to process a very large data, you will not need to use it.
I hope this article will be useful to you and help you apply these normalization rules in all projects. You may want to know where these methods come from
I can tell you that the first three normalization rules were mentioned in his paper "further formalizing the relational model of databases" in 1972.
The rest of the rules are post-theoretical collections of theories and relational mathematicians. Comment: the so-called thing level is opposite. It is sometimes not good to divide the table too carefully,
In this case, you need to associate the tables, which will make the query complex and reduce the efficiency. For more information about the normalization rules, see
When necessary, some tests can be carried out based on the project size to design a more reasonable table structure.