Mastering Coupon System Design: Database Schemas and UI Integration

Related Table Structures

Coupon Table

Stores coupon information; the usage type determines the scope: 0‑All, 1‑Specific category, 2‑Specific product.

<code>create table sms_coupon (
  id bigint not null auto_increment,
  type int(1) comment 'Coupon type; 0‑All, 1‑Member, 2‑Shopping, 3‑Register',
  name varchar(100) comment 'Name',
  platform int(1) comment 'Platform: 0‑All, 1‑Mobile, 2‑PC',
  count int comment 'Quantity',
  amount decimal(10,2) comment 'Amount',
  per_limit int comment 'Per‑person limit',
  min_point decimal(10,2) comment 'Minimum points required; 0 means none',
  start_time datetime comment 'Start time',
  end_time datetime comment 'End time',
  use_type int(1) comment 'Use type: 0‑All, 1‑Category, 2‑Product',
  note varchar(200) comment 'Remark',
  publish_count int comment 'Publish count',
  use_count int comment 'Used count',
  receive_count int comment 'Received count',
  enable_time datetime comment 'Receive date',
  code varchar(64) comment 'Coupon code',
  member_level int(1) comment 'Member level allowed; 0‑No restriction',
  primary key (id)
);</code>

Coupon History Table

Records each member's coupon acquisition and usage; use_status indicates 0‑Unused, 1‑Used, 2‑Expired.

<code>create table sms_coupon_history (
  id bigint not null auto_increment,
  coupon_id bigint comment 'Coupon ID',
  member_id bigint comment 'Member ID',
  order_id bigint comment 'Order ID',
  coupon_code varchar(64) comment 'Coupon code',
  member_nickname varchar(64) comment 'Member nickname',
  get_type int(1) comment 'Acquisition type: 0‑Admin grant, 1‑User claim',
  create_time datetime comment 'Creation time',
  use_status int comment 'Use status: 0‑Unused, 1‑Used, 2‑Expired',
  use_time datetime comment 'Use time',
  order_sn varchar(100) comment 'Order number',
  primary key (id)
);</code>

Coupon‑Product Relation Table

Links coupons to specific products when use_type is set to product‑specific.

<code>create table sms_coupon_product_relation (
  id bigint not null auto_increment,
  coupon_id bigint comment 'Coupon ID',
  product_id bigint comment 'Product ID',
  product_name varchar(500) comment 'Product name',
  product_sn varchar(200) comment 'Product SKU',
  primary key (id)
);</code>

Coupon‑Product Category Relation Table

Links coupons to product categories when use_type is category‑specific.

<code>create table sms_coupon_product_category_relation (
  id bigint not null auto_increment,
  coupon_id bigint comment 'Coupon ID',
  product_category_id bigint comment 'Category ID',
  product_category_name varchar(200) comment 'Category name',
  parent_category_name varchar(200) comment 'Parent category name',
  primary key (id)
);</code>

Admin Interface

Coupon List

Edit Coupon

All‑Site Coupon

Specific Product

Specific Category

View Coupon

Mobile Interface

My Coupons

Unused

Used

Expired

Coupon Details