Order Database Design Details (RPM) in e-commerce (electronic pin) platform

The following is their own in the e-commerce system design of the order module database design experience Summary, and now published to share, if inappropriate, welcome thread discussion ~

Order
|--AutoNumber (order_id, self-growing primary key)
|--order number (Order_no, unique value for customer inquiries)
|--Store code (SHOP_ID, Store table AutoNumber)
|--order status (Order_status, unpaid, paid, shipped, signed, return request, return, returned, cancelled)
|--number of items (product_count, number of items, not merchandise)
| --Total price of goods (product_amount_total)
|--Order Amount (Order_amount_total, actual payment amount)
|--Freight Amount (logistics_fee)
|--Whether unpacking inspection (is_ Unpacking_inspection)
|--whether invoicing (invoicing)
|--invoice number (Order Invoice table AutoNumber)
|--receipt Address number (ADDRESS_ID, receipt Address Table AutoNumber)
|--Order Logistics number (ORDERLOGISTICS_ID, order Logistics table auto-numbering)
|--Order Payment Channel (Pay_channel)
|--Order Payment ticket (OUT_TRADE_NO/ESCROW_TRADE_NO, third party payment serial number)
|--creation time (next order)
|--payment Time
|--Delivery time
|--customer number (user_id, User table AutoNumber)
|--Customer Comments
|--Order settlement status (order_settlement_status, cod, installments)
|--order settlement time (order_settlement_time)

Order invoice Form (Order_invoice)
|--automatic numbering (invoice_id)
|--Order Number (ORDER_ID)
|--VAT Invoice (Is_vat, free text invoice, value added invoice)
|--Invoice Header name (Invoice_title)
|--Invoice Header content (invoice_content)
|--Invoice Amount (invoice_amount)
|--Invoice Tax Number (INVOICE_TAX_NO)
|--Invoicing Tax (invoice_tax)
|--company name [VAT] (Vat_company_name)
|--company address [VAT] (vat_company_address)
|--contact number [VAT] (Vat_telphone)
|--depositary Bank [VAT] (Vat_bank_name)
|--Bank account number [VAT] (Vat_bank_account)
|--Invoicing Time (Created_time)

Order Logistics Form (Order_logistics)
|--automatic numbering (orderlogistics_id)
|--Order Number (order_id, Order table Auto-numbering)
|--Logistics Number (EXPRESS_NO, delivery tracking number)
|--Consignee name (consignee_realname, receipt Address table may be updated or deleted, so record here)
|--Contact Number (Consignee_telphone, the delivery address table may be updated or deleted, so record here)
|--Alternate Contact Number (Consignee_telphone2, the delivery address table may be updated or deleted, so record here)
|--Receipt Address (consignee_address, the delivery address table may be updated or deleted, so record here)
|--Post Code (consignee_zip, Shipping Address table may be updated or deleted, so record here)
|--Logistics Mode (Logistics_type, EMS, Express)
|--Logistics Merchant Number (LOGISTICS_ID, logistics merchant Table Auto-numbering)
|--Logistics Shipping Freight (Logistics_fee, show to Customer's order shipping)
|--Express Collection of payment rates (Agency_fee, courier companies collect payment rates, such as the value of 2%-5%, the general monthly knot)
|--Logistics Cost Amount (Delivery_amount, amount actually paid to the logistics company)
|--Logistics Status (orderlogistics_status)
|--Logistics Settlement status (Logistics_settlement_status, not settled, settled, partial settlement)
|--Logistics Final Status description (Logistics_result_last)
|--Logistics Description (Logistics_result)
|--delivery Time (Logistics_create_time)
|--Logistics Update Time (Logistics_update_time)
|--Logistics Settlement Time (Logistics_settlement_time)
|--Logistics Payment Channel
|--Logistics Payment Tracking number
|--Logistics company has reconciled status (Reconciliation_status, reconciled, not reconciled)
|--Logistics company Reconciliation Date (Reconciliation_time)
Design Note: The delivery address may be modified, deleted, etc., so here to record the shipping address of the user, so that even if the delivery address is deleted, the user in the view of the history of the order will still be able to see the delivery address snapshot information.

Order return form (order_returns)
|--AutoNumber (order_returns_id)
|--return number (Returns_no, for customer inquiries)
|-- Order number (order_id, order form Auto-numbering)
|--Logistics Number (EXPRESS_NO, return logistics)
|--consignee name (consignee_realname)
|--Contact Number (consignee_ Telphone)
|--Alternate Contact phone (consignee_telphone2)
|--delivery address (consignee_address)
|--zip code (consignee_zip)
|-- Logistics Mode (Logistics_type, EMS, Express)
|--Logistics merchant number
|--Logistics Shipping (Logistics_fee, return shipping)
|--Logistics status (Orderlogistics_ Status)
|--Logistics Final Status Description
|--Logistics Description
|--Logistics Update time
|--Logistics delivery time
|--return type (Returns_type, all orders, partial return)
|--return processing ( Handling_way, Pupaway: return storage; Redelivery: Re-shipment; Reclaim-redelivery: No return and re-shipment is required; REFUND: Refunds; Compensation: Do not return and compensate)
|--refund Amount (Returns_amount)
|--The cost of return sales (Seller_punish_fee)
|--return request time (Return_submit _time)
|--return processing time (handling_time)
|--return reason
Design Note: Returns may be modified, deleted, etc., so here to record the return address information of the merchant,

Order Item Details table (order_detail)
|--AutoNumber
|--order number
|--Product Code
|--product Name (product_name, product may be deleted, So here to record, can not directly read the commodity table)
|--commodity Price (Product_price, the item may be deleted, so here to record)
|--product model (Product_marque, front desk display to customer)
|--Commodity Barcode ( Product_store_barcode, Commodity Warehouse barcode)
|--product model information (PRODUCT_MODE_DESC, record detailed product type, such as color, specifications, packaging, etc.)
|--product model parameters (Product_mode_ params, JSON format, record unit number, color number, specification number, etc.)
|--discount ratio (discount_rate)
|--discount amount (discount_amount)
|--Purchase Quantity (number)
|--Subtotal Amount (subtotal)
|--product is valid (is_product_exists)
|--customer product notes (remark)
Design Note: Items may be modified, deleted, etc. So here to record the order when the user concerned about the commodity transaction summary information, such as price, quantity, model, model parameters and so on. This way even after the product is deleted, the user can still see the snapshot information of the product when they view the historical order.

Receipt Address Table (delivery_address)
|--automatic numbering (address_id)
|--User number (user_id, User table Auto-numbering)
|--Recipient name (realname)
|--Contact Number (Telphone)
|--Alternate Contact number (Telphone2)
|--Country (country)
|--Province (province)
|--City
|--(area)
|--Street/Detailed delivery address (street)
|--post Code (Zip)
|--whether the default delivery address (is_default_address)
|--creation Time (Created_time)

Shopping Cart Table (ShoppingCart)
|--automatic numbering (ID)
|--user Number (USER_ID)
|--Store Number (shop_id)
|--Product Code (PRODUCT_ID)
Whether the |--is valid (is_product_exists)
|--Purchase Quantity (number)
|--creation Time (Created_time)
Design Note: The commodity price and the subtotal amount are to be read and calculated by the real-time Association commodity table, because the merchant may change the commodity price, or the product is sold out, or the product has been down, etc., so here only need to record the product ID, commodity price, etc. to be read from the commodity table in real time.

=============================== Extended design of order modules for telemarketing ======================================

order Business approval process table (order_auditbiz)
|--AutoNumber (order_auditbiz_id)
|--order number (order_id)
|--order status (0: No audit or initiation of the transaction; 1: Transaction completed; 20: nuclear single pass; 24: nuclear single failure; 30: Shipped, unsigned, 34: Warehouse returned, 40: seat cancellation, 41: Buyer cancelled, 42: Overdue cancellation, 43: Invalid order cancellation, 50: Customer sign-off; 54: customer's refusal; 55: Customer's return
|--the sales staff directly to confirm the order (do not need the order auditor confirmation, direct mandatory approval, such as customer returns, the salesperson must bear the return shipping) (is_seller_risk_confirm)
|--Order returns, the salesperson is responsible for shipping (Is_seller _punish
_logistics_fee)
|--Sales Member (is_seller_commission)
|--Salesperson percentage (seller_commission_rate, no commission, fill 0)
|--Salesperson Commission Amount (seller_commission_amount)
|--Salesperson Order notes (Seller_remark, notes to the order Auditor)
|--Order Reviewer Order notes (confirmer_ Remark, notes to the warehouse tube)
|--bin notes (Storekeeper_returnback_remark, notes returned to the Order auditor)
|--Financial notes (Cashier_remark, notes to the salesperson)
|--Salesperson User number (SELLER_UID)
|--Order Auditor user number (AUDITOR_UID)
|--Payee user number (Cashier_uid, payee is not necessarily financial)
|--Financial user number ( Accountant_uid, financial Personnel user number)
|--order source (Order_source, sales order, internal purchase)
|--Order Auditor Approval Time (auditor_audited_time)
|-- Warehouse Controller audit Time (storekeeper_audited_time)
|--Financial Review Time (Accountant_audited_time)

Order Commission form (order_commission)
|--automatic numbering (order_commission_id)
|--Order Number (ORDER_ID)
|--Sales Member User number (SELLER_UID)
|--Commission Amount (Commission_amount)
|--settlement status (Settlement_status)
|--settlement Time (Settlement_time)
|--Financial Personnel user number (CASHIER_UID)

Order Scheduling Table (Order_dispatch)
|--Automatic Numbering
|--Order Number
|--dispatched Marketing Personnel user number (FROM_SELLER_UID)
|--Marketing Staff user number (TO_SELLER_UID)
|--scheduling Reason (Dispatch_reason)
|--Dispatch Administrator (DIAPATCH_ADMIN_UID)
|--scheduling Date (created_time)

The database design principles are:

1. To improve read performance, split the write operation into another table as much as possible, because updating the table causes the lock table to degrade the read performance of the data table.
2. Some associated information may later be modified or deleted at the time of transaction, such as merchandise, receiving address, etc., so in order to record the merchandise information and delivery address of the transaction, while the goods or the delivery address is deleted, you can still see the snapshot information in the history order.
3. Do not be afraid to split into a number of tables, read more than one table associated with reading, it will be more than reading a field a lot of data large table efficiency is much higher.

Order Database Design Details (RPM) in e-commerce (electronic pin) platform