vivo Global Mall: Universal pickup code design for e-commerce platforms

Author｜vivo official website mall development team - Zhou Longjian

1. Background

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

In daily life, most of our experience with functions such as pickup codes and verification comes from: picking up tickets before watching a movie, showing coupon codes after eating, picking up packages at express lockers, etc. They all have some similar characteristics, such as:

• The pickup code is relatively short. Compared with the order number of dozens or even hundreds of digits, the digital code of several digits is easier to remember and input.
• In addition to the digital pickup code, a QR code is also provided for easy scanning and verification at the terminal.

The pickup code is very simple to use, but like an "iceberg", it requires rigorous design and meticulous logic hidden under the simple appearance. It can be said that it is small but complete. The design introduced in this article is also quite interesting, and according to this idea, it can realize the generation of most verification voucher codes on the market, and it can also meet the SaaS of the business. It is a relatively common capability. Here I share the whole design with you.

2. Single-table business of simple system

If the business volume is small, the store traffic is relatively small, and the platform scale has not been formed, such as a system for individual operators. Then the implementation of the pickup code or coupon code is relatively simple. Just share a large horizontal table with the order or use an extended table to associate with the order. 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 order number is generally long, usually in the 10s or 20s (of course, there are shorter order numbers. If the order number is shorter, the pickup code can also use the order number). Let's assume that the order number is 18 digits and the pickup code is 8 digits, that is, the value range of the order number is much larger than the pickup code. Then, within the life cycle of the order number, there is a high probability that the pickup code will be repeated. The solution is relatively simple. We only need to ensure that under any conditions, the unverified digital code is not repeated, that is, the verified digital code can be recycled.

Then the generation logic of the pickup code is very clear. The following pseudo code simulates the actual implementation logic:

Pseudocode implementation

 for (;;) {
 step1 Get the random code: String code = this.getRandomCode();
 step2 Execute SQL: SELECT COUNT(1) FROM order_main WHERE code = ${code} AND write_off_status = 0;
 Step 3 Determine whether insertion is possible: if ( count > 0) { continue; }
 step4 Execute data writing: UPDATE order_main SET code = ${code}, qr_code = ${qrCode}, write_off_status = 0 WHERE order_no = ${orderNo}
 } 

*Note: Step 2 and step 4 are not atomic operations, and there are concurrency issues. In actual applications, it is best to use distributed locks to lock the operations.

3. Database and table sharding services for complex platforms

Through the simple single-table design, we can get a glimpse of the general implementation logic of the pickup code. However, when we implement a simple solution on a large project, we need to consider many aspects and the design needs to be more sophisticated. The SaaS e-commerce platform is much more complicated than a simple single-table business, and the key points are:

1. SaaS products involve many stores and a large number of orders, so large-capacity storage is required. Therefore, the order table is basically divided into sub-libraries and sub-tables. Obviously, the pickup code table attached to the order must also use the same strategy;
2. The user experience of the B-end and C-end users is very important. The design of the server-side interface needs to fully consider robustness and improve the most basic retry and fault tolerance capabilities.
3. Different business parties may have different requirements for pickup codes, and the design of pickup codes needs to have universal and personalized configuration attributes.

3.1 Detailed design

The design of the pickup code table recommends using a database and table sharding strategy that is 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 database and table sharding factors for querying (for example: open_id, member_id).

When considering the implementation, the first point of discussion we encountered was whether the pickup code should be "store-unique" or "globally unique"?

3.2 Store-only solution

At first, we considered using a logic similar to the restaurant pickup code to ensure that the pickup code remains unique in each store. Similar to the interaction shown in the following figure, user A and user B hold the same pickup code. Users A and B go to their corresponding stores to complete the verification, and the entire transaction process is over. However, this requires ensuring that users A and B can correctly complete the verification at the stores to which their orders belong. Obviously, this solution is risky!

In the case shown in the figure below, users A and B can also cancel orders normally, but the orders are mixed up, and the order originally belonging to user A is cancelled by user B. The fundamental reason for this problem is that the pure digital code cannot carry the user's identity. Although it can be avoided by manually verifying the identity before cancellation, it is still a high-risk system design, so the store-only solution is not advisable!

3.3 Globally unique solution

The global unique solution has low risk, but is slightly more difficult to implement. The core issue is how to determine whether the randomly generated pickup code is globally unique. Of course, if the system itself relies on storage media such as ES, you can query ES before inserting it, but querying and writing ES is a bit heavy for real-time interfaces, and it is not as direct as directly querying the library table. Suppose a business party is divided into 4 libraries and 4 tables, a total of 16 tables, and the length of the pickup code is determined to be 8 digits. How to query and ensure global uniqueness in Mysql with multiple libraries and tables? Traversing the table is definitely not advisable!

To solve the above questions, we can do something about the arrangement of the pickup code during design. The following steps are explained in detail:

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

Step 2: The random code area will not be introduced for now. Let's take a look at how the 2-bit library table is mapped to 16 tables consisting of 4 libraries and 4 tables.

There are also two solutions here:

[Solution 1] You can choose 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 simpler, but the capacity is not large enough. If the number of libraries or tables is greater than 9, expansion will be a bit troublesome. As shown in the figure below, we logically map the last "12" to "table number 2 in library 1";

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

After a simple arrangement of the pickup code, we completed the mapping logic of the pickup code to the database table, solving the problem of accessing the pickup code. In fact, if you think about it carefully, the problem of global uniqueness has also been solved. We only need to ensure that the first 6 random codes are unique in a single table. In theory, the range of supported records in a single table in the unwritten-off state is: 000000 ~ 999999, and the capacity is sufficient. The key is that we have simplified the query of multiple databases and tables to only run one SQL, which greatly improves efficiency.

3.4 Problems encountered in implementing the solution

Since this article introduces a complete SaaS solution, you will encounter some problems when implementing it. Here are three typical problems encountered in practice and some solutions:

[Question 1] The 6-digit random code generated by Math.random() is the same as the one in the table. How to deal with it?

[Solution] In fact, there are two types of duplication:

1. There may be a pickup code with the same number that has not been verified in the table;
2. Another situation is that other transactions are in operation, and a distributed transaction happens to lock the same digital code (the probability is very low, but it is possible).

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

 // step1 Get the database and table ID according to the database and table partitioning factors, userCode-user ID, tenantId-tenant ID
 String suffix = getCodeSuffix(userCode, tenantId);
 // step2 Get 6-digit random codes in batches
 for (int i=1; i<=5; i++) {
 // Get random numbers in batches. Each time you retry, filter the number by an exponential number of 2. Compared with brute-force for loops, this method can reduce the interaction with the DB.
 List<String> tempCodes = getRandomCodes(2 << i);
 // Filter out distributed locks
 filterDistributeLock(tempCodes);
 // Filter out the random codes that exist in the database
 filterExistsCodes(tempCodes);
 return tempCodes;
 }
 // step3 process random code and store it in the database
 for (String code : codes) {
 // Lock and determine whether the lock is successful. It is recommended to use Redis distributed lock
 boolean hasLockd = isLocked(code);
 try {
 // Execute storage
 insert(object);
 finally
 // Unlock
 }
 }
 // step4 executes the logic of post-QR code image, etc. 

【Notice】

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

[Question 2] How to dynamically modify the data source when using sharding components (such as ShardingSphere-JDBC) in the project? That is, to support sharding factors (such as member_id, open_id, etc.) and dynamic query of the database table calculated based on the pickup code.

[Solution] We use ShardingSphere-JDBC as an example to give some configurations and pseudocodes. For details, please refer to: "Forced Routing:: ShardingSphere". Other open source sharding components or self-developed products will not be described in detail. You can write a plug-in manually. Don't be afraid. No matter how difficult it is, you must believe in light!

Configuration and pseudo code

 // ShardingSphere-JDBC depends on the configuration file jdbc-sharding.yaml
 ...
 ShardingRule:
 tables:
 ...
 # Pickup code table
 order_code:
 actualDataNodes: DS00$->{0..3}.order_pick_up_0$->{0..3}
 #Configure the calculation logic of the library
 databaseStrategy:
 hint:
 algorithmClassName: com.xxx.xxxxx.xxx.service.impl.DbHintShardingAlgorithm
 # Calculation logic of the spouse table
 tableStrategy:
 hint:
 algorithmClassName: com.xxx.xxxxx.xxx.service.impl.DbHintShardingAlgorithm
 ...
 // java code
 try (HintManager hintManager = HintManager.getInstance()) {
 hintManager.addDatabaseShardingValue("order_code"/** Pickup code table*/, DbHintShardingAlgorithm.calDbShardingValue(tenantId, code));
 hintManager.addTableShardingValue("order_code"/** Pickup code table*/, DbHintShardingAlgorithm.calTabShardingValue(tenantId, code));
 Object xxx = xxxMapper.selectOne(queryDTO);
 } 

【Notice】

1. Here we introduce a programmatic solution, which has the advantages of simple configuration and flexibility, but the disadvantage is that it has a little more code. In fact, ShardingSphere also supports annotations, you can study it yourself;
2. The first point is more flexible, which is reflected in the self-implemented "DbHintShardingAlgorithm.calDbShardingValue(tenantId, code)" method. This method can be defined by ourselves, so our input parameters can be general database and table sharding factors, or the "database and table location" field of a custom pickup code, which is very flexible.

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

[Solution] Careful friends should have noticed the "tenantId" field, which is a tenant code, which 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 the pickup code is arranged, the strategy for mapping the pickup code to the database table location, etc., as long as the main logic is further abstracted and the strategy mode is used for personalized coding.

IV. Conclusion

When implementing the pickup code logic, I found that there were relatively few solutions and technical articles for online coupon codes. At that time, I had the idea of ​​writing an article to share my experience. In fact, I believe that most companies may do the same thing to a certain extent, and even if they adopt other solutions, they can achieve the same result. This article as a whole only introduces an idea, and this idea is similar to a simplified version of order sub-library and sub-table, but this is the magic. In fact, we can also implement some commonly used technical solutions in different application scenarios, make some bold attempts, and take more paths that have never been imagined!