Designing Routing Keys for Sharding in an Order Platform

The author, a Java architect, introduces the challenge of choosing a routing key after applying database sharding (分库分表), emphasizing that the key must exist uniquely in each table and promote even data distribution.

Two common approaches are discussed: using a timestamp as the routing key for archival workloads, which creates time‑based tables, and designing a business‑related key that aligns with the domain to balance load across shards.

In the context of a food‑delivery order platform, the system must support real‑time order status queries for users (C‑side) and analytical queries for merchants (B‑side); because orders may be written to different tables, queries often need to span multiple tables.

The article stresses that without a deterministic routing rule, queries would have to scan all tables, whereas a rule‑based routing strategy allows both user and merchant queries to follow the same logic, reducing complexity.

For the user side, the recommended routing key is user_id . In a single‑database setup, the user_id is hashed and the result modulo the number of tables determines the target table. In a multi‑database, multi‑table setup, the hash modulo the number of databases selects the database, and a second modulo operation selects the table within that database.

The merchant side uses a similar approach with merchant_id , keeping B‑side tables separate from C‑side tables. Order IDs are sent through a message queue; merchants consume the messages, retrieve order details, and insert them into their own sharded tables using the same hash‑modulo routing logic.

A complete data‑flow diagram (illustrated in the original article) shows the end‑to‑end process from order creation, routing key calculation, MQ transmission, to final storage in the appropriate shard.

The article concludes with a call to share the content and join the architect community for further learning.