About database table field redundancy

Today because of the database table design problems, by @ Sweet Potato said a pass. Exposed to a few of their own design problems, after thinking, found that they are still the pattern Tucson broken Ah! Here's a very thought-out question to say.

Suppose there is a scene. There are a few tables that I have designed.

• User Table [User]:id,username
• Project table [Project]:id,projectname, user_id
• Version table [version]:id,versionname,project_id
  
• Classification table [category]:id,categoryname,version_id
• Table of Contents [content]:id,text,category_id

That's pretty much it. Looking very good, no field redundancy.

After being taught by @ sweet potatoes ...

Ask a question first, named Problem X.

Question x: If you want to query the list of content under a version, SQL writes this:

Select c.* from content C, category T where C.category_id=t.id and t.version_id=?

There seems to be no problem. How do you optimize this query?

This is the last question we'll ever say. Speaking back to the above table design, first of all, there is a problem. For example, I want to query whether content a belongs to user u, what should I do?

1. Query the category B to which content a belongs
2. Query the version c to which category B belongs
3. Querying the project that version C belongs to D
4. Query whether item d belongs to user U, and thus whether content a belongs to user u

Such a practice is simply bad. At this point I was acutely aware of my weak explosion. How do we do that?

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

First, you can determine that the value of the USER_ID field for each table will not change. Therefore, after the value of this field is set from the beginning, it is no longer necessary to modify it.

Then we go back to the question: Does the query for content a belong to user U. Here's how it's done:

1. Query whether the user_id of content A is the ID of user U

Just one step! Good simple rough!

Just add user_id this redundant field, it is very convenient to encode the amount, and the database query volume is also reduced by N times. Also, this field only needs to be maintained once!

Now, back to question X. How do you optimize that business logic?

What I do now is: in the Content table, add one more version_id field, and you can be sure that this field is similar to the user_id field and needs to be maintained only once.

Then the SQL for problem x is changed to:

Select c.* from content C where c.version_id=?

Such a simple sql!

The above description, practice out of the truth! Sometimes, proper database redundancy is a good choice.

About database table field redundancy