Design and Implementation of Pickup Code System for SaaS E-commerce Platform

This article introduces the design and implementation of a pickup code (取货码) system for e-commerce platforms, covering scenarios from simple single-table implementations to complex distributed database architectures.

Background: With the expansion of O2O online-to-offline business, e-commerce platforms need pickup code generation and order verification functionality. Similar to movie ticket pickup, restaurant voucher redemption, and package collection, pickup codes are short numeric codes (typically 6-8 digits) that are easier to remember than long order numbers, often accompanied by QR codes for terminal scanning.

Simple Single-Table Design: For small-scale businesses, pickup codes can share the same table with orders or use extension tables. The key consideration is ensuring unique unverified numeric codes, while verified codes can be recycled. The pseudo-code implementation involves generating random codes and checking for duplicates before insertion.

Complex Distributed Platform Design: For SaaS platforms with many stores and large order volumes, the design becomes more sophisticated:

Orders use database sharding, requiring pickup code tables to follow the same strategy

Interface design must consider robustness, retry, and fault tolerance

The design needs to support generalization and personalized configuration

Store-Unique vs Global-Unique: The store-unique approach (similar to restaurant queue numbers) carries risks where different users might have the same code at different stores, potentially leading to order verification errors. The global-unique solution is preferred despite higher implementation complexity.

Global Unique Implementation: The 8-digit pickup code is divided into two parts: a 6-digit random code area and a 2-digit database/table position area. Two mapping schemes are proposed: (1) using the first digit as database number and last digit as table number, or (2) converting the 2D structure (4 databases × 4 tables = 16 tables) into a 1D sequence (0-15). This approach simplifies multi-database queries into a single SQL operation.

Practical Challenges and Solutions:

Problem 1: Random code conflicts - Solution involves exponential retry (2 << i) to reduce database interactions, with distributed locks (Redis) for code insertion.

Problem 2: Dynamic datasource modification with sharding components like ShardingSphere-JDBC - Solution uses HintManager for forced routing, allowing dynamic specification of sharding values based on tenant ID and code.

Problem 3: Extensibility for different business scenarios - Solution uses tenant ID for configurable parameters including code length, encoding method, and database mapping strategy, utilizing the strategy pattern for personalized implementation.