Designing Optimal Order Status Fields: Best Practices for Database Schemas

Problem Overview

During the design of an order and payment system, the team disagreed on how to model the "order status" column. This article examines common points of contention and proposes deeper analysis of shared issues.

Business Analysis

The order lifecycle includes create → pay → deliver → receive → return, with additional actions such as refund and comment. The business state (BizState) changes with each action, while the database field (OrderState) records a subset of these states.

Figure0: Order business process flow

Core actions (create, pay, deliver, receive) have dependent sub‑states (0 = not started, 1 = in progress, 2 = success, 3 = failure). Many combinations are meaningless and are discarded.

Table1: BizState combinations under multiple actions

Core Statuses

Waiting for buyer payment Buyer payment successful Seller has shipped Buyer has received

Figure1: Base order state flow

Failure Sub‑states

Failure of the create action results in a terminal state "order creation failed". Payment failure may be recorded as "payment failed" depending on requirements. Failures of delivery or receipt are usually handled offline and not stored.

Order creation failed (terminal) Waiting for buyer payment Payment failed (terminal, requirement‑dependent) Buyer payment successful Seller has shipped Buyer has received

Figure2: Adding failure sub‑states

In‑Progress Sub‑states

Payment in progress is recorded as "payment confirmation in progress" for user visibility. In‑progress delivery or receipt are treated as "buyer has paid" in the OrderState.

Order creation failed (terminal) Waiting for buyer payment Payment confirmation in progress Payment failed (terminal, requirement‑dependent) Buyer payment successful Seller has shipped Buyer has received

Figure3: Adding in‑progress sub‑states

Comment Handling

Comments are not core to the order flow and should be stored in a separate field (CommentState) with values "not commented", "buyer commented", "seller commented".

Return Handling

Order creation failed (terminal) Waiting for buyer payment Payment confirmation in progress Payment failed (terminal, requirement‑dependent) Buyer payment successful Seller has shipped Buyer has received Return in progress Return successful (terminal)

Figure4: Adding return sub‑states

Refund Handling

Refunds are independent of returns and should be recorded in a separate field (RefundState) with values "refund in progress" and "refund successful".

Other Considerations

Order creation failed (terminal) Waiting for buyer payment Payment confirmation in progress Payment failed (terminal, requirement‑dependent) Buyer payment successful Seller has shipped Buyer has received Return in progress Return successful (terminal) Order closed (terminal)

Conclusion

Core actions that are single‑directionally dependent should have their BizState stored in the main OrderState column. The final OrderState dictionary contains ten values, four of which are terminal states.

Figure5: Final order state flow

Question 2: Representation of OrderState Dictionary Values

Options: numeric identifiers, multi‑bit flags, or meaningful English strings. Numeric identifiers are simple but obscure meaning; multi‑bit flags add complexity; English strings are clear but longer. A hybrid approach (Option d) creates a separate dictionary table storing numeric IDs, English names, Chinese abbreviations, and explanations, while the OrderState column stores the numeric ID.

Question 3: Data Type for Status Column

Considering query patterns, performance, and storage, varchar2(N) is chosen over number(N) and char(N) because it offers comparable performance with smaller storage footprint.

Key Takeaways

If an action belongs to the core order flow and depends only on preceding actions, its BizState should be recorded in the main status column.

Comments, refunds, and similar actions that have multiple dependencies should be stored in dedicated fields.