Designing Routing Keys for Sharding in an Order Platform

After splitting a database into multiple tables, the first problem is selecting a routing key that exists uniquely in each table and ensures even data distribution.

For archival workloads, time‑based keys (e.g., creation timestamp) can be used to create tables per month or quarter, keeping historical data in older tables while recent data stays in the latest tables.

Business‑related keys can also be designed to align with traffic patterns, ensuring each database can handle the load.

Supported Scenarios : In a food‑delivery order platform, users need real‑time order status, while merchants analyze orders for decision‑making. Queries may need to span multiple tables because orders can be stored in different shards.

Routing Strategy : If orders are inserted randomly, queries must scan all tables. A better approach is to define deterministic routing rules so that both insertion and query use the same logic, reducing complexity.

User‑Side Routing Key : Use user_id as the routing key. In a single‑database setup, hash the user_id and modulo by the number of tables to determine the target table. In a multi‑database, multi‑table setup, first hash the user_id to select the database, then hash again (or divide) to select the table.

Business‑Side Routing Key : Merchants use a separate set of tables with merchant_id as the routing key. The routing logic mirrors the user side: hash the merchant_id to pick the database and table.

The complete data flow diagram shows how orders are routed from the user side to the merchant side via MQ, with both sides following the same routing strategy.

In summary, the key to effective sharding routing is to select a key closely related to the business entity, apply a hash‑modulo algorithm to distribute data evenly across databases and tables, and ensure both insertion and query follow the same deterministic rule.