Design of redundant fields for database design

When you design a database, a field belongs to one table, but it appears in the other or more tables at the same time, and is exactly the same as the meaning of the table it belongs to, then this field is a redundant field.

--These are the definitions I gave myself.

Is the existence of redundant fields good or bad (

Redundancy is for efficiency and reduces join. Single-table queries are faster than associated queries.
A field that is frequently accessed can be stored redundantly in two tables without being associated.

）？ This is a difficult question to say. It may seem to someone that this is a very crappy database design. Because in the field of database design, there is a mantras of the database design paradigm, this paradigm requires a clear database design logic, the relationship is clear, for example, "User nickname" Field "nickname" originally belongs to the table "user", then, the "username nickname" field should only belong to the "User" table "nickname" field, so that when the user to modify the nickname, the program only need to modify the User.nickname This field is OK, see, very convenient . But the problem comes with the fact that I only store the ID of the user in other datasheets (such as the Orders table), and what do I do with this ID worth the user's nickname? A common workaround is to use a join to fetch the user's nickname by joining two tables through the unique condition of the ID at the time of the query.

This is really no problem, I have always felt that this is the best solution, the extension is convenient, when to update user information, the program to modify the place is very few, but with the data in the database continues to increase, million, tens of millions, at the same time, the user table data is certainly constantly increasing, it may be 100,000, million. At this time, you will find that two tables through the join to fetch data is very laborious, perhaps you just need to take a nickname the user nickname attribute, you have to go to the link that already hundreds of thousands of user table to retrieve, its speed is conceivable.

At this point, you can try to add the nickname field to the Orders table, so the good thing is that when you want to render a list of orders through the order form, the part that involves the user may not need to join the query again (into a single table query). Of course, there are pros and cons, the disadvantage is that when you try to update the user information, you must remember the User Information table is currently updated in the field, there are redundant fields, which are the table, find them, and then add to your update section. This is the cost of the program, the cost of the developer's time. As to whether it is worthwhile to do so, it depends on the specific circumstances.

So, for now, to create a relational database design, we have two options:

1. Try to follow the canonical paradigm, with as few redundant fields as possible, making the database design look exquisite, elegant, and fascinating.
2. A reasonable addition to the redundant field of this lubricant, reduce join, so that the performance of the database to perform higher and faster.

Which one to choose? If you are an aesthetic maniac and have deep pockets, it's okay to use the first option, and the short board of this scheme is not hopeless. For example, you can increase the server, starting from the database cluster , read and write separation, reading the pressure can be dispersed to different database servers, which can also achieve good performance, but pay more hardware costs and maintenance costs. Alternatively, you can set up caching services such as memcached in the front of the database, reducing the number of read and write databases, and achieving the same effect. The problem is you're sure you need to cache things like that.

Reproduced in: http://blog.sina.com.cn/u/3232608592

Design of redundant fields for database design