Designing Effective Order Status Fields: Best Practices and Schema Choices

1. Problem Overview

Team members disagree on how to design the OrderState column, and various online discussions raise similar questions. The article explores common issues and proposes deeper analysis.

2. Business Analysis

The order lifecycle includes create → pay → deliver → receive → return, with optional refund and comment actions. The diagram below illustrates the flow.

Each action can be in one of four sub‑states: not started (0), in progress (1), success (2), or failure (3). The theoretical combination count is 4⁵=1024, but many are meaningless and can be discarded.

Core Statuses

Waiting for buyer payment

Buyer payment successful

Seller shipped

Buyer received

The current state transition diagram is shown below.

Action Failure Cases

If the create action fails, the order is marked as “order creation failed” (terminal). Payment failure may be recorded as “payment failed” depending on requirements. Delivery and receipt failures are usually handled offline and not stored.

Action In‑Progress Cases

Payment in progress is recorded as “payment confirmation in progress”. Delivery and receipt in‑progress states are omitted because they do not affect the user‑visible status.

Comment Action

Comments are not part of the core flow; they should be stored in a separate CommentState field with values such as “not commented”, “buyer commented”, “seller commented”.

Return Action

Return is a core action dependent on receipt, so “return in progress” and “return completed” are added to the order status.

Other Considerations

Order cancellation/closure can be added as a terminal state.

Conclusion

Core actions that are single‑direction dependent should have their business states stored in the main OrderState column. The final set contains ten states, four of which are terminal.

If an action belongs to the core business flow and depends only on preceding actions, its resulting business state must be recorded in the entity’s primary status field.

3. Dictionary Representation Options

Three approaches are considered:

Numeric codes (e.g., 0 for “waiting for payment”).

Bit‑mask representation (e.g., 0111 for “shipped, not commented”).

English string enums (e.g., WAIT_BUYER_PAY).

Numeric codes are simple; bit‑mask adds unnecessary complexity; string enums are readable but longer. A hybrid solution (option d) stores a numeric code in OrderState while maintaining a separate dictionary table that maps the number to English and Chinese descriptions.

4. Field Type Choice

Possible types are NUMBER(N), CHAR(N), and VARCHAR2(N). Benchmarks show CHAR and VARCHAR2 outperform NUMBER for equality queries. VARCHAR2 uses the least storage, so it is recommended.

5. Summary of Conclusions

Core business actions (create, pay, deliver, receive, return) belong in OrderState.

Comments and refunds should be stored in separate fields.

Use a numeric enum stored in OrderState with an auxiliary dictionary table for readability.

Choose VARCHAR2(N) as the column type.

6. References

Database table design – status fields; Performance analysis of NUMBER, CHAR, VARCHAR2 in Oracle 10g.