Database Design Tips [reprint] _ MySQL

Database design skills [Reprinted] database design skills (1)
--------------------------------------------------------------------------------

Author: allsky
In the design of dynamic websites, the importance of database design is self-evident. If the design is not proper, the query will be very difficult and the program performance will be affected. None
You are using a mySQL or Oracle Database. by designing a regular table, you can make your PHP code more readable and scalable.
It also improves application performance.

To put it simply, normalization is to eliminate redundancy and uncoordinated subordination during table design. In this article, I will tell you through five gradual processes
Normalization skills you should understand in design. So as to establish a feasible and
Efficient databases. This article also analyzes in detail the available link types.

Assume that we want to create a user information table, which stores the user name, company, company address, and personal favorites or URLs. On
At the beginning, you may define the following table structure:

Zero status

Users

Name company company_address url1 url2

Joe ABC 1 Work Lane abc.com xyz.com

Jill XYZ 1 Job Street abc.com xyz.com

Since no normalization is performed, we call this form of table as a zero-state table. Pay attention to the url1 and url2 fields --- if we
What if the third url is required for the application? In this way, you need to add a column to the table. Obviously, this is not a good solution. If you want to create a scalable
System, you must consider using the first formal form and apply it to this table.

Level 1 normalization

1. remove duplicate groups in each table

2. create an independent table for each set of related data

3. use a primary key to identify each set of related data

The preceding table clearly violates the first rule. what does the primary key of the third statement mean? It's easy. it just adds one to each record.
Unique and automatically added integer values. With this value, two records with the same name can be distinguished. Through the first-level normalization form of the application, we
The following table is displayed:

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 table can be said to be in the first-level Normalization Form. it has solved the restriction problem of url fields. However, after such processing
A new problem. Every time we insert a record in the user table, we must repeat all the company and user data. This not only makes the database
It is too big and prone to errors. Therefore, it must undergo second-level regularization.

Database design skills (2)
--------------------------------------------------------------------------------

Author: allsky
1. create an independent table for the application's fields in multiple records

2. associate the values of these tables with a foreign key.

We put the url value in a separate table so that we can add more data in the future without worrying about duplicate values. We still pass
Through the primary key value to associate these fields:

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, an independent table is created. the primary key userid in the users table is now associated with the foreign key relUserId in the url table. Current situation
It seems that it has been significantly improved. But what if we want to add an employee record for ABC? Or more, 200? In this way, we must
Reuse of company names and addresses is not redundant. Therefore, we will apply the third-level normalization method:

Level 3 Regularization

1. eliminate fields that do not depend on the key

The company name and address have no relationship with the User Id, so their applications 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

In this way, we associate the primary key comId in the companies table with the foreign key named relCompId in the users table, even if ABC joins
There are only one record for 200 employees in companies. Our users and urls tables can be expanded constantly without worrying about inserting unnecessary data. Most
Developers think that the three-step normalization is sufficient. the database design can easily handle the burden on the entire enterprise.
Number is correct.

We can pay attention to the URL field-have you noticed data redundancy? If the HTML page for users to enter the url data is a text
Box, if you can enter any information, this is no problem. The two users have less probability to enter the same favorites. However, if you use a drop-down menu
Select two URLs or a bit more. In this case, our database can also be optimized at the next level-step 4 for most
For the sender, this step is ignored because it depends on a very special relationship-a many-to-many relationship, which has never been encountered in our applications.

Database design skills (III)
--------------------------------------------------------------------------------

Author: allsky
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.