Designing the Evolution of an Order Middle Platform: From Pain Points to a Big‑Platform + Small‑Chimney Architecture

Current Situation and Pain Points

Existing “big middle platform” concept lacks a standard for order creation/return integration, causing deviation during iteration.

Separate ordering and sales systems create concurrent risks and delayed data sync due to dual‑write paths.

ERP backend stores all orders in a single menu; the order master table has grown to hundreds of columns and may reach thousands after two years, making JOIN queries expensive.

Future order intake forms, integration standards, architectural style, and storage strategies need to be defined.

Clear responsibility boundaries for the order middle platform must be established to enable rapid onboarding of new business.

Order Storage Strategies

Standardizing Core Fields in Master Records

Using DDD’s four‑color modeling, an order is a time‑stamp model; the state at the moment of ordering should be persisted (snapshot redundancy). Therefore, fields such as user nickname and product name are stored directly in the master record to avoid costly JOINs.

If a user changes their nickname after placing an order, the historical order cannot be found by the new nickname. The recommended approach is to provide a front‑end control that gathers various search criteria (nickname, phone, etc.), resolves the internal user ID, and passes it to the backend for querying.

Extended Information Storage Solutions

Option 1: Order Type + Vertical Extension Tables

Orders are split into multiple vertical extension tables linked one‑to‑many with the order type. New order types that cannot fit existing tables trigger the creation of new extension tables.

Advantages : optimal SQL join performance and data‑analysis speed.

Disadvantages : if order types differ greatly, vertical tables proliferate; reusing tables for new types may impact existing business.

Option 2: Order Type + Wide Order Table

All fields are placed in a single wide table.

Advantages : simple implementation, SQL join performance remains optimal.

Disadvantages : large variance among order types leads to rapid column explosion, many missing fields, and requires schema changes for new types. With 16 existing order types and a master table already containing hundreds of columns, this approach is unsuitable.

Option 3: Order Type + JSON Extension

Dynamic attributes are stored in a JSON column (MySQL 5.7+) or MongoDB.

Food‑delivery extras: {"delivery_address": "Beijing Chaoyang", "rider_id": 1001}

E‑commerce extras: {"sku_list": [{"id":101, "qty":2}], "invoice_type": "electronic"}

Advantages : no schema changes needed for new order types.

Disadvantages : JSON fields are unfriendly to backend conditional queries and SQL analytics.

Option 4: Order Type + Order Extension KV Table

Define a metadata table order_ext_meta (order_type, field, remark, field_type) and an extension table order_ext (order_id, key, value).

order_ext_meta example row:
order_type = 1 (online order), field = ext1, remark = "Extension Field One", field_type = STRING

order_ext example rows for order SO123:
(SO123, ext1, "Extension Value One")
(SO123, ext2, "Extension Value Two")

Queries require pivoting rows to columns (e.g., MySQL dynamic SQL, PostgreSQL crosstab) or application‑level transformation.

Advantages : extension fields can be JOINed and fully analyzed via SQL; adding a new order type only updates metadata.

Disadvantages : double‑table operations increase query complexity; value type is fixed and must be validated in the application.

Final Architecture of the Order Middle Platform

Implementation is divided into two stages.

Customer order/return/query flow is unified in the ordering system, which extracts query logic to a separate query system while delegating ordering capabilities to the middle platform.

The unified architectural style treats the ordering system as a “small chimney” base, on par with other backend services. Business systems call the middle‑platform APIs for order creation, return, and modification, while order‑specific data can reside in the respective business DBs and be synchronized via MQ.

Core Use Cases

Customer query: C‑end website → Business system → Business DB read replica → On‑demand aggregation of middle‑platform order data.

Customer order/return: C‑end website → Business system → Business DB write → Order middle platform → PolarDB write.

Operational query: ERP workbench → Query system → PolarDB read‑only proxy → PolarDB read replica → On‑demand aggregation of business‑specific order data.

Operational modification: ERP workbench → Order middle platform → PolarDB write → Publish MQ domain event → Business systems listen for their order type.

Responsibility Boundary Division

Order/Return – Platform provides APIs to business systems; Business systems expose functionality to C‑end website and ERP workbench.

Order/Return Validation – Platform performs standard validation; Business systems handle custom validation.

Customer Query – Platform provides APIs to business systems; Business systems aggregate middle‑platform data directly.

Operational Query – Unified query system provided by both platform and business systems.

Operational Modification – Platform publishes MQ after modification; Business systems use dual‑write strategy and roll back on failure.

Auto‑Cancel Strategy – Platform scans tables, closes orders, publishes MQ; Business systems listen to cancel events.

Field Storage Strategy – Platform stores only standard fields, retains existing non‑standard fields; Business systems store differentiated fields and redundantly store standard fields as needed.

Payment Result Notification – Platform sends payment result MQ event; Business systems listen to the event.

Order State Machine – Platform stores standard order states; Business systems extend with custom states.

Order Delivery Info – Platform optionally receives detailed delivery info; Business systems may store redundantly.

Order Invoicing – Platform optionally receives invoicing info; Business systems may store redundantly.

Lifecycle Management – Platform provides unified control flow; Business systems can issue commands to the platform.

Warehouse Capability – Platform optionally supports inbound/outbound and inventory adjustment.

Marketing Capability – Platform optionally handles order discounts (cards, coupons, points) dependent on membership and product systems.

Product Capability – Platform supports product and non‑product order creation.

After‑sale Capability – Platform initiates after‑sale processes and approval flows.

Domain Event Notification – Platform publishes generic order domain events; Business systems publish specific domain events.

Customer Transaction Main Flow