Database design skills three_PHP tutorial

Before defining the fourth normalized form, I would like to first mention three basic data relationships: one-to-one, one-to-many and many-to-many. Let’s look back at the first normalized users table. If we put the url field in a separate table, each time we insert a record in the users table, we will insert a row in the urls
table. We will get a one-to-one relationship: for every row in the users table, there will be a corresponding row in the urls table. For our application, this is neither practical nor standard.
Then look at the second regularization example. For each user record, our table allows for multiple urls of records to be associated with it. This is a one-to-many
relationship, which is a very common relationship.
For many-to-many relationships, it’s a bit complicated. In our third normalized form example, we have one user associated with a lot of urls, and
we want to change the structure to allow multiple users to be associated with multiple urls, so that we can get A many-to-many structure. Before discussing, let’s first take a look at the changes in the table
grid structure
users
userId name relCompId
1 Joe 1
2 Jill 2
companies
compId company company_address
1 ABC 1 Work Lane
2 🎜>　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 regularization form. We created a rather strange url_relations table, and the fields in it are all
primary keys or foreign keys. Through this table, we can eliminate duplicate items in the urls table. The following are the specific requirements of the fourth normalized form:
The fourth normalized form
1. In a many-to-many relationship, independent entities cannot be stored in the same table

Since it only applies to many-to-many relationships, most developers can ignore this rule. However, in some cases, it can be very practical, as is the case with this
example, where we improved the urls table by separating out the same entities and moving the relationships into their own table.
To make it easier for you to understand, let’s give a specific example. The following will use a 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 iterate through everyone’s personal information and url information, we can do this:
SELECT name, url FROM users, urls, url_relationsswheresusers .userId = url_relations.relatedUserId AND
urls.urlId = url_relations.relatedUrlId

The fifth level of normalization form
There is also the first level of normalization form, which is uncommon, a bit esoteric, and used in most In all cases it is unnecessary. Its principle is:
1. The original table must be reconstructed through the table separated from it
The advantage of using this provision is that you can ensure that no redundant columns will be introduced in the separated table. The table structures you create are always as large as they need to be
. It's a good practice to apply this rule, but unless you're dealing with a very large data set, you won't need to use it.
I hope this article is useful to you and can help you apply these formalization rules in all your projects. You may be wondering where these methods come from. I can tell you that the first three regularization regulations were proposed by Dr. E.F. Codd in 1972 in his paper "Further Normalizing the Relational Model of Databases"
came out, and the rest of the regulations were theorized by later set theory and relational mathematicians. Comment: As the saying goes, things must be reversed. Sometimes it is not good to divide the tables too finely.
Because this requires various associations between the tables, which will complicate the query and may also reduce the efficiency. These Formalization regulations can be referred to. In actual application, according to the size of the project, some tests can be carried out if necessary to design a more reasonable table structure.




http://www.bkjia.com/PHPjc/531975.html

www.bkjia.com

true

http: //www.bkjia.com/PHPjc/531975.html
TechArticle