shoppingmall

Tables (20)

address

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • recipient — VARCHAR(100) — NOT NULL
  • phone — VARCHAR(30)
  • zip_code — VARCHAR(20)
  • line1 — VARCHAR(200) — NOT NULL
  • line2 — VARCHAR(200)
  • city — VARCHAR(100)
  • state — VARCHAR(100)
  • country — VARCHAR(100)
  • is_default — TINYINT(1)
  • created_at — TIMESTAMP

cart

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

cart_item

  • id — BIGINT
  • cart_id — BIGINT — NOT NULL
  • variant_id — BIGINT — NOT NULL
  • qty — INT — NOT NULL
  • price_at — DECIMAL(12,2) — NOT NULL

category

  • id — BIGINT
  • parent_id — BIGINT
  • name — VARCHAR(120) — NOT NULL
  • slug — VARCHAR(160) — NOT NULL
  • depth — INT

coupon

  • id — BIGINT
  • code — VARCHAR(40) — NOT NULL
  • name — VARCHAR(150) — NOT NULL
  • type — ENUM('FIXED','PERCENT') — NOT NULL
  • amount — DECIMAL(12,2) — NOT NULL
  • min_order — DECIMAL(12,2)
  • max_discount — DECIMAL(12,2)
  • start_at — DATETIME
  • end_at — DATETIME
  • issued_qty — INT
  • used_qty — INT
  • is_active — TINYINT(1)

coupon_redemption

  • id — BIGINT
  • coupon_id — BIGINT — NOT NULL
  • user_id — BIGINT — NOT NULL
  • order_id — BIGINT — NOT NULL
  • used_at — DATETIME

inventory

  • variant_id — BIGINT
  • stock_qty — INT — NOT NULL
  • safe_qty — INT — NOT NULL
  • updated_at — TIMESTAMP

order

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • order_no — VARCHAR(40) — NOT NULL
  • status — ENUM('PENDING','PAID','PREPARING','SHIPPED','COMPLETED','CANCELLED','REFUNDED')
  • subtotal_amount — DECIMAL(12,2) — NOT NULL
  • discount_amount — DECIMAL(12,2) — NOT NULL
  • shipping_fee — DECIMAL(12,2) — NOT NULL
  • total_amount — DECIMAL(12,2) — NOT NULL
  • receiver_name — VARCHAR(100) — NOT NULL
  • receiver_phone — VARCHAR(30)
  • address_json — JSON — NOT NULL
  • memo — VARCHAR(300)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

order_item

  • id — BIGINT
  • order_id — BIGINT — NOT NULL
  • product_id — BIGINT — NOT NULL
  • variant_id — BIGINT — NOT NULL
  • name_snapshot — VARCHAR(220) — NOT NULL
  • option_json — JSON
  • unit_price — DECIMAL(12,2) — NOT NULL
  • qty — INT — NOT NULL
  • line_total — DECIMAL(12,2) — NOT NULL

payment

  • id — BIGINT
  • order_id — BIGINT — NOT NULL
  • provider — VARCHAR(50) — NOT NULL
  • method — VARCHAR(50) — NOT NULL
  • amount — DECIMAL(12,2) — NOT NULL
  • status — ENUM('INIT','PAID','FAILED','CANCELLED')
  • tx_id — VARCHAR(120)
  • paid_at — DATETIME
  • created_at — TIMESTAMP

product

  • id — BIGINT
  • name — VARCHAR(200) — NOT NULL
  • slug — VARCHAR(220) — NOT NULL
  • description — TEXT
  • base_price — DECIMAL(12,2) — NOT NULL
  • status — ENUM('DRAFT','ON_SALE','HIDDEN')
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

product_category

  • product_id — BIGINT — Primary key — NOT NULL
  • category_id — BIGINT — Primary key — NOT NULL

product_image

  • id — BIGINT
  • product_id — BIGINT — NOT NULL
  • url — VARCHAR(500) — NOT NULL
  • alt_text — VARCHAR(200)
  • sort_order — INT

product_variant

  • id — BIGINT
  • product_id — BIGINT — NOT NULL
  • sku — VARCHAR(120) — NOT NULL
  • option_json — JSON
  • price — DECIMAL(12,2) — NOT NULL
  • barcode — VARCHAR(120)
  • is_active — TINYINT(1)

review

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • product_id — BIGINT — NOT NULL
  • rating — TINYINT — NOT NULL
  • title — VARCHAR(150)
  • content — TEXT
  • created_at — TIMESTAMP

role

  • id — BIGINT
  • name — VARCHAR(50) — NOT NULL

shipment

  • id — BIGINT
  • order_id — BIGINT — NOT NULL
  • carrier — VARCHAR(60)
  • tracking_no — VARCHAR(100)
  • status — ENUM('READY','IN_TRANSIT','DELIVERED','RETURNED')
  • shipped_at — DATETIME
  • delivered_at — DATETIME
  • created_at — TIMESTAMP

user

  • id — BIGINT
  • email — VARCHAR(190) — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • phone — VARCHAR(30)
  • status — ENUM('ACTIVE','SUSPENDED')
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

user_role

  • user_id — BIGINT — Primary key — NOT NULL
  • role_id — BIGINT — Primary key — NOT NULL

wishlist

  • user_id — BIGINT — Primary key — NOT NULL
  • product_id — BIGINT — Primary key — NOT NULL
  • created_at — TIMESTAMP