An inaccessible express address, a lazy table design lesson, and a lazy Express Delivery

An inaccessible express address, a lazy table design lesson, and a lazy Express Delivery

Some time ago, when the mall was doing promotions, our testers also bought some products, but they had not received the express delivery after several days, so they were wondering. After confirmation by developers, the mail address for this order is xxx, Mars, Beijing, with a telephone number of 15555555555. This is obviously not a reasonable mail address. Since it has been confirmed that there is no possibility of SQL Injection online, the normal online process may only affect the mail address of the order. When checking the online process, I found a huge bug, which seems to be unremarkable, but in fact it has a large impact scope.

In the early stage of the establishment of the mall, only the function of adding common addresses for users is provided, and the function of deleting and changing addresses is not provided. That is to say, once an address is added, the id and address information of this address are read-only and unchanged. Therefore, in order to save space, the id number of the user address is directly referenced in the order, as a snapshot of the address when the user submits the order, that is, the database table is structured as follows:

 

The problem lies here!

With the optimization of the mall, the product MM felt that editing and deleting users' frequently-used addresses should be normal, but due to the interval of about one month, therefore, when adding this function, we completely forget that the user's order is associated with the user address id, which brings about a big problem: the user may be confused (or novel) and change his address to an existing address. In this way, the Order address is actually implicitly modified! This design is of course incorrect.

If you know the reason, you will know the solution: that is, when the order is generated, the snapshot information of the current address should be recorded instead of simply saving the address id. To minimize the size of the order table, a separate order_addr table is extracted to record the order address information. In this way, when searching order information, unnecessary address information will not slow down the entire order Table query, but when you need to find the address information, you only need to simply join the query.

The current table is actually associated like this:

 

After this modification, the user's modification or deletion of common addresses will not affect the order status.

Sometimes a seemingly simple problem may have many potential traps. This problem also warns us that the simpler the problem, the less light it can be, comprehensive thinking should be a good habit for us to do anything.