How to reasonably use the redundant fields of the database

privot is the intermediate table of many-to-many relationships. The PT5 framework will automatically bring up privot.

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

In addition to redundant fields, we have a table to record pictures and another table to record products.

We can put the URL of the product picture in the picture

At the same time, put the picture ID and picture URL in the product

These two fields are repeated, which is data redundancy. We design the database to avoid redundant information, so why do we use redundancy?

This is mainly for query performance considerations.

We have made data redundancy here, so we can reduce the number of queries to the image table and speed up the query!

However, it is recommended that you abuse data redundancy because data redundancy makes it difficult to maintain data integrity and consistency. There are two places that record the same information, but when we write data, we need to write it to two places. The biggest problem is deletion and updating. When updating, if the IMG in one place changes, you need to change both. Otherwise, data inconsistency will occur. However, data redundancy is still used quite a lot.

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

• User table [user]: id, userName
• Project table [project]: id, projectName, user_id
• Version table [version]: id, versionName, project_id
• Category table [category]: id, categoryName, version_id
• Content table [content]: id, text, category_id

That's about it. It looks good, with no redundant fields. It also conforms to the three major paradigms of database design.

Let's first ask a question, let's call it Question X.

Question X: If you want to query the content list under a certain version, the SQL should be written like this:

select c.* from content c, category t where c.category_id=t.id and t.version_id=?

It doesn't seem to be a problem. How to optimize this query? We will discuss this question last. Let’s go back to the table design above, if there is such a problem. For example, I want to check whether content A belongs to user U, what should I do?

• Search for category B to which content A belongs
• Query version C of category B
• Query project D to which version C belongs
• Query whether item D belongs to user U, and thus determine whether content A belongs to user U

This is extremely bad behavior, isn't it? At this point you should have realized the weakness of this table design. So what should we do?

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

First, you can be sure that the value of the user_id field in each table will not change. Therefore, once the value of this field is set, it does not need to be modified.

Then, we return to the above question: query whether content A belongs to user U. The current approach is this:

Check whether the user_id of content A is the id of user U

Just one step! So simple and crude, right? Pretty refreshing, right?

Simply adding the redundant field user_id can greatly reduce the amount of coding and increase the database query efficiency by 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 correct approach should be: add an additional version_id field in the content table. It is certain that this field is similar to the user_id field and only needs to be maintained once.

Then the SQL for question X is changed to:

select c.* from content c where c.version_id=?

Pretty simple sql!

The above shows that sometimes, appropriate database redundancy is a good choice.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links