Mastering Order Return Database Design: Schemas, Statuses, and UI Flow

This article analyzes the order return and order return reason configuration tables, presenting the database schema alongside functional descriptions.

Order Return

Related Table Structures

Order Return Application Table

The table stores member return application information with four status values: 0→Pending, 1→Returning, 2→Completed, 3→Rejected.

<code>create table oms_order_return_apply (
    id bigint not null auto_increment,
    order_id bigint comment 'Order ID',
    company_address_id bigint comment 'Shipping address table ID',
    product_id bigint comment 'Returned product ID',
    order_sn varchar(64) comment 'Order number',
    create_time datetime comment 'Application time',
    member_username varchar(64) comment 'Member username',
    return_amount decimal(10,2) comment 'Refund amount',
    return_name varchar(100) comment 'Returner name',
    return_phone varchar(100) comment 'Returner phone',
    status int(1) comment 'Application status: 0→Pending; 1→Returning; 2→Completed; 3→Rejected',
    handle_time datetime comment 'Handling time',
    product_pic varchar(500) comment 'Product image',
    product_name varchar(200) comment 'Product name',
    product_brand varchar(200) comment 'Product brand',
    product_attr varchar(500) comment 'Product attributes, e.g., color:red; size:XL;',
    product_count int comment 'Return quantity',
    product_price decimal(10,2) comment 'Product unit price',
    product_real_price decimal(10,2) comment 'Actual paid unit price',
    reason varchar(200) comment 'Reason',
    description varchar(500) comment 'Description',
    proof_pics varchar(1000) comment 'Proof images, comma-separated',
    handle_note varchar(500) comment 'Handling notes',
    handle_man varchar(100) comment 'Handler',
    receive_man varchar(100) comment 'Receiver',
    receive_time datetime comment 'Receiving time',
    receive_note varchar(500) comment 'Receiving notes',
    primary key (id)
);</code>

Company Address Table

Used to select the shipping address when processing a return application.

<code>create table oms_company_address (
    id bigint not null auto_increment,
    address_name varchar(200) comment 'Address name',
    send_status int(1) comment 'Default shipping address: 0→No; 1→Yes',
    receive_status int(1) comment 'Default receiving address: 0→No; 1→Yes',
    name varchar(64) comment 'Recipient name',
    phone varchar(64) comment 'Recipient phone',
    province varchar(64) comment 'Province/municipality',
    city varchar(64) comment 'City',
    region varchar(64) comment 'Region',
    detail_address varchar(200) comment 'Detailed address',
    primary key (id)
);</code>

Admin Interface

Return application list

Details of pending status

Details of returning status

Details of completed status

Details of rejected status

Mobile Interface

Open after‑sales service in "My" section

Tap "Apply for Return" to start a return request

Submit return request

View return request records

Check return request progress details

Order Return Reason Settings

Order Return Reason Table

Used for members to select a reason when returning an order.

<code>create table oms_return_reason (
    id bigint not null auto_increment,
    address_name varchar(200) comment 'Address name',
    send_status int(1) comment 'Default shipping address: 0→No; 1→Yes',
    receive_status int(1) comment 'Default receiving address: 0→No; 1→Yes',
    name varchar(64) comment 'Recipient name',
    phone varchar(64) comment 'Recipient phone',
    province varchar(64) comment 'Province/municipality',
    city varchar(64) comment 'City',
    region varchar(64) comment 'Region',
    detail_address varchar(200) comment 'Detailed address',
    primary key (id)
);</code>

Admin Interface for Reasons

Return reason list

Add return reason

Mobile Interface for Reasons

Select return reason during application