How to better design datasheets?
problem description
here are two data tables: order,user. Table relationship order.user_id Association user.id .
Design a scene:
in the background management, we need to search for orders, such as searching according to time and user name.
one: the previous practice is to only store user.id in the order table, and indicate the table relationship through order.user_id . Then conduct an association search in the program.
2: the current idea is as shown above: save user.username in order . The idea is to facilitate search and reduce table associations
question
1: which of the two approaches is good, or different advantages or disadvantages.
2: in the second approach, there is a problem if the user modifies username . Then the fields in order must be modified accordingly. How to deal with it better? My current idea is: one is to modify the username in the corresponding order when the user changes the user name. The second uses triggers (not practiced).
Thank you!
- when the amount of data is small, you can consider saving only the associated fields
- if you have a large amount of data, you can consider appropriate data redundancy. As far as order is concerned, in fact, the frequency of username changes is relatively small, and the probability of showing differences is relatively small. To not affect the key data, I think we can also use Synchronize, or go to Synchronize regularly for a long time
.
if you have a large amount of data and a large number of visitors, it is recommended to use professional search tools such as elasticsearch.
if the amount of data is small and the traffic is mediocre, then you don't use elasticsearch, then you just use mysql. It is recommended to follow the paradigm design.
how is it designed? I think it is best to divide it into three tables.
1, order table
2, user table
3, order user search relation table.
in fact, the effect of Table 3 is the same as that of elasticsearch. 3 tables actually store redundant data, exchanging space for time.
how to solve the impact caused by username changes, username changes, the data in Table 3 need to be updated.
how many orders can a username correspond to, hundreds? Thousands? Tens of thousands? I don't think so. Tens of thousands at the most. And how often is username updated? One day? A month?
come to think of it this way, it's not a hassle for username to update Table 3 every time.
in fact, you can put redundant data in Table 1. But I think an order is an order, and the user is the user. Keep them independent, and it will be easy for you to expand in the future.
imagine that if your company gets bigger in the future, the boss needs you to use elasticsearch to solve the search problem. You store the redundant data in Table 1, which is equal to this part of the redundant data is actually useless.
but if I store this part of redundant data in Table 3, I can simply delete Table 3. It has no impact on the business and does not generate junk data.
is actually unnecessary, because you show that it is an one-to-one relationship. When you select, you only get all the user fields you need. Do not take all the user fields you need. The second way username stores order table
indicates that username is an attribute of this order at that time. It is not necessary to modify the username field of user to adjust all the username
corresponding to this user in order . To use a realistic example, if Xiaoming sent a courier whose name was Xiao Ming last year, and Xiaoming's name changed to Xiao Zhang a year later, the express name will also be changed to Xiao Zhang, which just indicates the name of the sender of the express delivery at that time.
