Mastering E‑Commerce Cart & Order Flow: Database Schema and Process Walkthrough

Shopping Cart Table

This table stores each product added to the cart and is used for calculating promotional discounts.

create table oms_cart_item (
    id bigint not null auto_increment,
    product_id bigint comment '商品的id',
    product_sku_id bigint comment '商品sku的id',
    member_id bigint comment '会员id',
    quantity int comment '购买数量',
    price decimal(10,2) comment '添加到购物车的价格',
    sp1 varchar(200) comment '销售属性1',
    sp2 varchar(200) comment '销售属性2',
    sp3 varchar(200) comment '销售属性3',
    product_pic varchar(1000) comment '商品主图',
    product_name varchar(500) comment '商品名称',
    product_brand varchar(200) comment '商品品牌',
    product_sn varchar(200) comment '商品的条码',
    product_sub_title varchar(500) comment '商品副标题（卖点）',
    product_sku_code varchar(200) comment '商品sku条码',
    member_nickname varchar(500) comment '会员昵称',
    create_date datetime comment '创建时间',
    modify_date datetime comment '修改时间',
    delete_status int(1) default 0 comment '是否删除',
    product_category_id bigint comment '商品的分类',
    product_attr varchar(500) comment '商品销售属性:[{"key":"颜色","value":"银色"},{"key":"容量","value":"4G"}]',
    primary key (id)
);

Order Process Flow

Overall Flow Diagram

Mobile Flow

Member selects product specifications

Select items in cart for checkout

View confirmation order

Pay order

Payment success

View order

Implementation Logic

Add to Cart

The cart stores user‑selected product information and calculates cart‑level discounts.

Cart Discount Calculation

Key Points

Discounts are designed per product, not per SKU, so calculations must be product‑based.

Refer to OmsPromotionServiceImpl.calcCartPromotion for code implementation.

Generate Confirmation Order

The confirmation order lets users verify product, discount, and price details, and choose shipping address, coupons, and points.

Confirmation Flow

Key Points

Total amount = sum of cart item prices.

Promotional discount = sum of discounts for all cart items.

Payable amount = total amount – promotional discount.

See OmsPortalOrderServiceImpl.generateConfirmOrder for implementation.

Generate Order

Processes cart information together with user data to create the final order.

Order Creation Flow

Key Points

Stock locking occurs when fetching cart promotion info; pms_sku_stock.lock_stock records locked quantity.

Coupon amount is split by scope: site‑wide, category‑specific, or product‑specific.

Actual payment per item = original price – promotion – coupon – points.

Order number generated via Redis: 8‑digit date + 2‑digit platform + 2‑digit payment method + incremental ID.

Coupon status is updated after use.

Refer to OmsPortalOrderServiceImpl.generateOrder for implementation.

Cancel Order

After order creation, a delayed task cancels orders that exceed the timeout.

Cancellation Flow

Key Points

See OmsPortalOrderServiceImpl.cancelOrder for the cancellation logic.