vivo Global Mall: Design of Universal Pickup Code for E-commerce Platforms

vivo official website mall development team - Zhou Longjian

1. Background

With the continuous expansion of O2O online and offline business, e-commerce platforms are gradually improving the product functions related to the transaction side. In the latest version of demand, in order to further improve the user experience, the business side has planned the logic related to the generation of the pick-up code and the verification of the order. The purpose is to allow online users to pick up the goods in the store or arrange a shopping guide after paying the payment. delivery.

In daily life, most of our experience in using functions such as pick-up code and write-off comes from: picking up tickets before watching a movie, showing the coupon code after eating, picking up packages from express cabinets, etc. They all have some similar characteristics. for example:

• The length of the pick-up code is relatively short, which is more convenient to memorize and input than the order number with a dozen or twenty digits and a few digits.
• In addition to the digital pickup code, a QR code is also provided, which is convenient for the terminal to scan and write off.

The pick-up code is very simple to use. However, like the "iceberg", it needs rigorous design and meticulous logic hidden under the simple appearance. It can be said that the sparrow is small and complete. The design introduced in this article is also interesting, and according to this idea, the generation of most write-off coupon codes on the market can be realized, and it can also satisfy the SaaSization of the business. It is a relatively general ability. .

(Image credit: pixabay.com )

2. Simple system single-table business

If the business volume is not large, the store traffic is relatively small, and the scale of the platform has not been formed, such as a system for self-employed operators. Then the implementation of the pick-up code or coupon code is relatively simple. It is enough to share a large horizontal table with the order or use the expansion table to associate it with the order, and there is no need to do excessive design at this stage.

The design of the table is as follows:

However, it should be noted that the general order number is relatively long, usually in the ten or twenty digits (of course, there are also relatively short order numbers. If the order number is relatively short, the order number can also be used for the pick-up code). We assume that the order number 18 digits and 8 digits for the pick-up code, that is, the value range of the order number is much larger than that of the pick-up code, so in the life cycle of the order number, the pick-up code is very likely to be duplicated. The solution is relatively simple. We only need to ensure that under any conditions, the digital codes in the unwritten state are not repeated, that is, the digital codes that have been written off can be recycled.

Then the generation logic of the pick-up code is very clear. Let's simulate the real implementation logic with pseudo code:

Pseudo code implementation

 for (;;) {
   step1 获取随机码：String code = this.getRandomCode();
   step2 执行SQL：SELECT COUNT(1) FROM order_main WHERE code = ${code} AND write_off_status = 0;
   step3 判断是否可以插入：if ( count > 0) { continue; }
   step4 执行数据写入：UPDATE order_main SET code = ${code}, qr_code = ${qrCode}, write_off_status = 0 WHERE order_no = ${orderNo}
}

*Note: Here step2 and step4 are not atomic operations, and there are concurrency problems. In practical applications, it is best to use distributed locks to lock the operations.

3. Sub-database and sub-table business for complex platforms

Through a simple single-table design, we can get a glimpse and understand the general implementation logic of the pick-up code. However, when we implement simple solutions to large-scale projects, we need to consider many aspects, and the design needs to be more sophisticated. A SaaS-based e-commerce platform is much more complicated than a simple single-table business. The key points are:

1. SaaS products involve many stores and have a large number of orders, so large-capacity storage needs to be designed, so the order table basically uses sub-library and sub-table, and obviously the pickup code table attached to the order must also use the same strategy;
2. The user experience of the B-side and C-side users is very important. The design of the server interface needs to fully consider the robustness and improve the most basic retry and fault tolerance capabilities;
3. Different business parties may have different requirements for the pick-up code, and the design of the pick-up code needs to have universality and personalized configuration attributes.

3.1 Detailed design

The design of the picking code table recommends the use of a sub-library and sub-table strategy consistent with the order. The benefits are:

1. Like orders, it supports the storage of massive order lines;
2. It is convenient to use the same sub-database sub-table factor to query (for example: open\_id, member\_id).

In considering the implementation, we encountered the first point of discussion, that is, is the pick-up code "unique in store" or "unique globally"?

3.2 The only solution for stores

At the beginning, I considered using a logic similar to the restaurant code to ensure that the code is unique in each store. Similar to the interaction in the figure below, in the figure, user A and user B hold the same pick-up code. Users A and B go to their corresponding stores to complete the write-off, and the entire transaction process is over. However, it is necessary to ensure that users A and B can correctly complete the write-off at the store where their respective orders belong. Obviously, this solution is risky!

In the case shown in the figure below, users A and B can also be written off normally, but the order that originally belonged to user A was written off by user B. The essential reason for this problem is that the pure digital code cannot carry the user's logo. Although it can be avoided by artificially verifying the identity before the write-off, it is still a high-risk system design, so the only solution for the store is not advisable!

3.3 Globally Unique Scheme

The globally unique solution is less risky, but slightly more difficult to implement. The core problem is how to determine that the randomly generated pick-up code is globally unique. Of course, if the system itself relies on storage media such as ES, you can query ES before inserting, but querying and writing ES is a little heavy for real-time interfaces. , there is no direct query library table directly. Suppose a business party is divided into 4 databases and 4 tables, a total of 16 tables, and the length of the pick-up code is determined to be 8 digits. How to query and ensure global uniqueness in Mysql with multiple databases and multiple tables? It's definitely not advisable to iterate over the table!

In order to solve the above questions, we can make some articles on the arrangement of the pick-up code during the design, and the following steps will be explained in detail:

Step 1: The 8-digit pickup code can be divided into two areas, "random code area" + "library table location" , as shown in the example below:

Step 2: The random code area will not be introduced for the time being. Let's see how the 2-bit library table is mapped to the 16 tables composed of 4 libraries and 4 tables.

There are also two options here:

[Option 1] You can select the first digit of the 2-digit library table as the library number, and the last digit as the table number. The advantage is that the mapping is relatively simple, but the capacity is not large enough. If the divided library or table is > 9, the expansion will be a bit troublesome. As shown in the figure below, we map the logic of "12" at the end to "table number 2 of library 1";

[Option 2] Convert the two-dimensional structure of 4 libraries and 4 tables into one dimension, and increment with 0 as the initial value, (0 library, 0 table) → 00, (0 library, 1 table) → 01... , (3 library, 3 tables) → 15. The advantage is that the capacity has become larger, and it supports a maximum of 99 tables, which is not limited by the library or form one condition. The disadvantage is that the mapping logic is troublesome to write, but this is not a problem.

After the pick-up code is simply arranged, we have completed the mapping logic of the pick-up code to the warehouse table, and solved the problem of access to the pick-up code. In fact, if you think about it carefully, the problem of global uniqueness has actually been solved. We only need to ensure that the first 6 random codes are guaranteed to be unique in a single table. In theory, the range of supported single table in the unwritten state is: 000000 ~ 999999 records, the capacity is sufficient. The key point is that we have simplified the query of multiple databases and multiple tables to only running one SQL, which greatly improves the efficiency.

3.4 Problems encountered in the implementation of the plan

Since this article is an introduction to the complete solution of SaaS, some problems will be encountered more or less when it is implemented. Here are three typical problems encountered in practice, and some solutions are given:

[Question 1] The 6-digit random code generated by Math.random() is duplicated in the table, what should I do?

[Solution] In fact, there are two cases of repetition:

1. It may be that there already exists a pickup code with the same number but not written off;
2. Another situation is that other transactions are operating, and a distributed transaction happens to lock the same number (probability is very low, but it is possible).

The emergence of these two situations requires us to retry gracefully! The general idea is as follows pseudo-code:

 // step1 根据分库分表因子获取库表编号，userCode-用户编号、tenantId-租户编号
String suffix = getCodeSuffix(userCode, tenantId);
 
// step2 批量获取6位随机码
for (int i=1; i<=5; i++) {
   // 批量获取随机数。每次重试，取2的指数级量进行过滤，相比暴力执行for循环，这种方式能减少和DB的交互
   List<String> tempCodes = getRandomCodes(2 << i);
   // 过滤掉分布式锁
   filterDistributeLock(tempCodes);
   // 过滤掉数据库存在的随机码
   filterExistsCodes(tempCodes);
   return tempCodes;
}
 
// step3 处理随机码，随机码入库
for (String code : codes) {
   // 加锁，判断加锁是否成功。推荐使用Redis分布式锁
   boolean hasLockd = isLocked(code);
   try {
         // 执行入库
         insert(object);
   } finally {
      // 解锁
   }
}
 
// step4 执行后置二维码图片等逻辑

【Notice】

1. It is recommended to use the exponential retry method (2 << i), gradually increase the number of random, and reduce the interaction with the DB;
2. It is recommended to lock and execute INSERT after the digital code is generated, and the time-consuming actions such as generating the image address can be post-UPDATE.

[Question 2] The project uses a sub-database and sub-table component (for example: ShardingSphere-JDBC), how to dynamically modify the data source? That is to say, it also supports sub-database sub-table factors (such as: member\_id, open\_id, etc.) and dynamic query of the library table calculated according to the pick-up code.

[Solution] We take ShardingSphere-JDBC as an example to give some configuration and pseudo-code. For details, please refer to: " Forced Routing::ShardingSphere ", other open source sub-database sub-table components or self-developed products will not be described in detail, you can do it yourself Write a plug-in manually, don't be afraid, no matter how difficult it is, believe in the light!

Configuration and Pseudocode

 // ShardingSphere-JDBC依赖的配置文件jdbc-sharding.yaml
...
shardingRule:
  tables:
    ...
    # 取货码表
    order_code:
      actualDataNodes: DS00$->{0..3}.order_pick_up_0$->{0..3}
      # 配置库的计算逻辑
      databaseStrategy:
        hint:
          algorithmClassName: com.xxx.xxxxx.xxx.service.impl.DbHintShardingAlgorithm
      # 配偶之表的计算逻辑
      tableStrategy:
        hint:
          algorithmClassName: com.xxx.xxxxx.xxx.service.impl.DbHintShardingAlgorithm
    ...
 
// java代码
try (HintManager hintManager = HintManager.getInstance()) {
    hintManager.addDatabaseShardingValue("order_code"/** 取货码表 */, DbHintShardingAlgorithm.calDbShardingValue(tenantId, code));
    hintManager.addTableShardingValue("order_code"/** 取货码表 */, DbHintShardingAlgorithm.calTabShardingValue(tenantId, code));
     
    Object xxx = xxxMapper.selectOne(queryDTO);
}

【Notice】

1. Here is a programmatic solution. The advantage is that the configuration is simple and flexible. The disadvantage is that the code is slightly more. In fact, ShardingSphere also supports the annotation method, you can study it yourself;
2. The first one said that it is more flexible, which is reflected in the "DbHintShardingAlgorithm.calDbShardingValue(tenantId, code)" method implemented by myself. This method can be defined by ourselves, so our input parameters can be general sub-database sub-table factors, or can be The "Stock table location" field of the customized pickup code is very flexible.

[Question 3] How to achieve stronger scalability and apply to SaaS platforms and different business scenarios?

[Solution] Careful friends should have noticed the "tenantId" field, which is the code of a tenant, and will be transparently transmitted in the actual code. We can use this field to make different configurations for different tenants (or business parties), such as: the length of the pickup code, the way to arrange the pickup code, the strategy for the location of the pickup code mapping library table, etc. Matching, as long as the backbone logic is further abstracted, and the strategy pattern is used for personalized coding.

4. Summary

When I realized the logic of picking up the code, I found that there were relatively few solutions and technical articles about the online coupon code. At that time, the idea of writing an article to attract others to share was born. In fact, I believe most companies are probably doing the same thing to some degree, even if they take other approaches. This article as a whole just introduces an idea, and this idea is similar to a simplified version of the order sub-database sub-table, but this is the magic, in fact, we can also apply some commonly used technical solutions to different application scenarios, boldly Do some experimentation and take more paths that have never been imagined!

Topic series:

• The evolution of the globalization of vivo global mall - multilingual solution
• vivo Global Mall: Commodity System Architecture Design and Practice
• Vivo Global Mall - Exploration of Marketing Price Monitoring Solution
• vivo Global Mall: Coupon System Architecture Design and Practice
• vivo Global Mall: Order Center Architecture Design and Practice
• vivo global mall: the integration of consignment business from 0 to 1
• vivo Global Mall: The Road to Architecture Evolution