Shulou(Shulou.com)06/01 Report--

The intermediate table of the privot many-to-many relationship. The PT5 framework automatically brings privot with it.

We need to hide, because we don't need privot, and pritvot is not in our model itself, it's intermediate data.

In addition to the redundant fields, we have one table that records pictures and another table that records goods.

We can put the url in the product picture in the picture.

At the same time, there are picture id and picture URL in the product.

These two fields are repeated, this is data redundancy, we design the database is not redundant information, why do we use redundancy?

Mainly for the sake of query performance.

We have done data redundancy here, so we can reduce the query to the picture table and speed up the query speed!

However, it is recommended that data redundancy be abused because it is difficult to maintain data integrity and consistency. There are two places to record the same information, but when we write data, we need to write to two places. The biggest problem is deleting and updating. During the update, the IMG in one place has changed, and two have to be changed. Otherwise, there will be data inconsistencies. However, a lot of data redundancy is used.

In WEB development, in addition to mastering some necessary database optimization skills, using redundant fields at the right time can also achieve twice the result with half the effort. For example, in the following example, there are several tables designed in this way.

User table [user]: id,userName item table [project]: id,projectName, user_id version table [version]: id,versionName,project_id classification table [category]: id,categoryName,version_id content table [content]: id,text,category_id

That's pretty much it. It looks good. There is no field redundancy. It is also in line with the three paradigms of database design.

Let's start with a question and name it question X.

Question X: if you want to query the list of contents under a certain version, sql should write like this:

Select c. * from content c, category t where c.category_id=t.id and t.versionstandards?

There seems to be nothing wrong with it. How do you optimize this query? We'll talk about this finally. Going back to the table design above, if there is such a problem. For example, if I want to query whether content A belongs to user U, what should I do?

Query category B to which content A belongs, query version B to which category B belongs, query item D to which version C belongs, query whether item D belongs to user U, and then determine whether content A belongs to user U.

It's a terrible thing to do, isn't it? By this time you should be well aware of the weakness of this kind of watch design. So how do you do that?

Redundant fields! Yes, we need to add redundant fields to the table. What if you add a user_id field to all of the above tables (except the user table)?

First, you can make sure that the value of the user_id field of each table does not change. Therefore, after the value of this field is set from the beginning, there is no need to modify it.

Then, let's go back to the above question: whether the query content A belongs to user U. The current practice is as follows:

Query whether the user_id of content An is the id of user U

Just one step! It's so simple and rough! It's refreshing, isn't it?

As long as the redundant field user_id is added, the amount of coding is greatly facilitated, and the query efficiency of the database is also improved N times. Also, this field only needs to be maintained once!

Now that you know the power of redundant fields, let's go back to question X. How to optimize that business logic?

The right thing to do is to add one more version_id field to the content table, which, to be sure, is similar to the user_id field and only needs to be maintained once.

Then the sql of question X is changed to:

Select c. * from content c where c. Versionstandards?

A fairly simple sql!

As mentioned above, sometimes proper database redundancy is a good choice.

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support. If you want to know more about it, please see the relevant links below.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.