Recently I’ve developed several e-commerce projects, which inevitably involved designing coupon systems.
Since the Monday Mall project didn’t have this module, I decided to implement it during this period.

Referencing JD.com’s style, created a template like this:

Here’s the core logic:

• First, we need a coupon template table
  • Stores coupon templates for backend generation and user redemption
  • Requires a separate user coupon table
  • Table structure overview:
    

    

# Template Table
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  
  `title` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Coupon title',  
  `amount` decimal(8,2) NOT NULL COMMENT 'Discount amount',  
  `full_amount` decimal(8,2) NOT NULL COMMENT 'Minimum purchase amount',  
  `score` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Redemption points required',  
  `start_date` date NOT NULL COMMENT 'Start date',  
  `end_date` date NOT NULL COMMENT 'Expiry date',  
  `created_at` timestamp NULL DEFAULT NULL COMMENT 'Creation time',  
  `updated_at` timestamp NULL DEFAULT NULL COMMENT 'Update time',  

# User Coupon Table
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  
  `user_id` int(10) unsigned COMMENT 'User ID',  
  `title` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Coupon title',  
  `amount` decimal(8,2) NOT NULL COMMENT 'Discount amount',  
  `full_amount` decimal(8,2) NOT NULL COMMENT 'Minimum purchase amount',  
  `start_date` date NOT NULL COMMENT 'Start date',  
  `end_date` date NOT NULL COMMENT 'Expiry date',  
  `used_at` timestamp NULL DEFAULT NULL COMMENT 'Usage time',  
  `created_at` timestamp NULL DEFAULT NULL COMMENT 'Creation time',  
  `updated_at` timestamp NULL DEFAULT NULL COMMENT 'Update time',  

Key workflow:

• Operations team creates coupon templates
  • Configure title, discount amount (set full_amount=0 for no minimum purchase)
  • Set points requirement and validity period
• Users claim coupons in activity center
  • Verify user points if required
  • Copy template fields to user coupon table
  • Apply single-coupon-type restriction if needed
• Coupon usage
  • Validate order amount against coupon’s full_amount during checkout
  • Mark used_at timestamp and link coupon to order
• Order refund
  • Process refund based on discounted amount
  • Remove coupon-order association
  • Reset used_at to null

An interesting JD-style feature worth implementing is redemption codes.
Create additional table:

# Redemption Code Table
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  
  `template_id` int(10) COMMENT 'Coupon template ID',  
  `code` varchar(191) COMMENT 'Redemption code',  
  `user_id` int(10) NULL COMMENT 'Redeemer ID',  
  `used_at` timestamp NULL DEFAULT NULL COMMENT 'Redemption time',  
  `created_at` timestamp NULL DEFAULT NULL COMMENT 'Creation time',  
  `updated_at` timestamp NULL DEFAULT NULL COMMENT 'Update time',  

Workflow:

• Operations pre-generates codes
  • Generate unique codes linked to templates
  • Optionally bind to specific users
• User redemption process
  • Input code in redemption zone
  • Validate code usage status and user binding
  • Generate user coupon from template
  • Mark code as used