8. Design Coupon System

Step 1. Scenario 场景

1.1 Coupon Types

• Rebate Coupon 满减券
  • For example, a "spend $100 and get$20 off" rebate coupon would mean that a customer needs to spend at least $100 in a single purchase to receive a$20 discount.
• Discount Coupon 折扣券
  • This type of coupon reduces the price by a certain percentage, such as 10% off, 20% off, or 50% off.
• Promotion Code 促销码
  • A promotion code is a voucher that offers special deals, or discounts to encourage customers to make a purchase.
  • These codes can take various forms, such as buy-one-get-one-free offers, free shipping, or additional free items with a purchase.

1.2 Core Concepts

• Issue Coupons 发券
  • Synchronous sending.
  • Asynchronous sending.
• Get Coupons 领券
  • Who: All users or specified users
  • Limit: The maximum number of coupons for one single user can get.
  • How: User to get coupons or system to send coupons to users.
• Use Coupons 用券
  • Scope: Product, category, or storewide.
  • Validation: Whether the coupon is valid.

1.3 Requirements Analysis

• Merchant Side
  • Create Coupons 创建优惠券
  • Distribute Coupons 发放优惠券
• User Side
  • Get Coupons
  • Place Order
  • Use Coupons
  • Payment
• Platform Side
  • Statistics and Tracking 统计与追踪
  • Amount Verification 金额核销

Step 2. Service 服务

设计优惠券系统 Coupon System 相关服务

2.1 Microservice Architecture

• Gateway
  • Payment Service and DB
  • Order Service and DB
  • Coupon Service and DB
  • Notification Service and DB

2.2 Challenges

• 1. Distributed Transaction Issues 券的分布式事务问题
• 2. Coupon over-issuance 如何防止超发券
• 3. Distribute Coupons to Users on a Large Scale 如何大规模发券
• 4. Limit Coupon Usage Conditions 如何限制券的使用条件
• 5. Getting Coupons Multiple Times 如何防止用户重复领券

Step 3. Storage 存储

3.1 Database Design

1. Coupon Batch (Coupon Template) 优惠券模版

• This refers to an abstract template representing a group of coupons. 指一批优惠券的抽象与模板
• The coupon batch or template allows businesses to easily generate multiple coupons with the same conditions

2. Coupon 优惠券

• A coupon refers to an individual entity that has been issued to a user and is belong to their account.

3. Rules 规则

• Coupon Usage Rules and Conditions, such as a "spend $100 and get$50 off"

3.2 Coupon - Create 创建

1. Create a coupon rule

INSERT INTO rule (name, type, rule_content)
VALUES("RULE_1", 0, '{
threshold: 100
amount: 10
......
}');

2. Create a coupon template

INSERT INTO coupon_batch (coupon_name, rule_id, total_count )
VALUES("Product_A_Coupon", 1010, 10000);

3.3 Coupon - Distribute 分发

1. Select user tags, groups and assign a coupon distribution task to the task queue. 选择用户标签，将发券任务分配给任务队列。
2. The task queue will assign the task to the worker.
3. Worker server will get the task and send the coupon to the user.

INSERT INTO coupon (user_id, coupon_id，batch_id)
VALUES(1001, 66889, 1111);

-- use transaction to ensure ACID
UPDATE coupon_batch SET total_count = total_count - 1, assign_count =
assign_count + 1
WHERE batch_id = 1111 AND total_count > 0;

3.4 Coupon - Notification Service 通知

• Message SMS
• Email
• Inbox Message

Challenge - Mass Push Notification

• Send messages only to active users. For example, out of 10 million users, only 20% are monthly active users, we only send to them. 只给活跃用户发通知。
• Check if the user is online before sending the message. 检查用户是否在线。
• Split the notification database table into multiple tables to save network traffic from repeatedly sending the same notification content. 将消息表分拆，以节约重复发送通知内容的网络流量。

3.5 Coupon - Get 领取

1. Check coupon balance 检查优惠券的数量

SELECT total_count FROM coupon_batch
WHERE batch_id = 1111;

2. Insert a row into coupon table and deduct the balance 新增数据并扣除余额

INSERT INTO coupon (user_id, coupon_id，batch_id)
VALUES(1001, 66889, 1111);

-- use transaction to ensure ACID
UPDATE coupon_batch SET total_count = total_count - 1, assign_count =
assign_count + 1
WHERE batch_id = 1111 AND total_count > 0;

Challenge - Prevent users from getting coupons multiple times

Solution 1: Redis Caching

1. Check the cache before getting coupons 领券前先查缓存

• 语法: SISMEMBER KEY VALUE
• 作用: 判断成员元素是否是集合的成员。
• 实例: SISMEMBER batch_id:1111:user_id 1001

2. Get coupons

3. Update the cache after getting coupons 领券后更新缓存

• 语法: SADD KEY VALUE1......VALUE
• 作用: 将一个或多个成员元素加入到集合中，已经存在于集合的成员元素将被忽略。
• 实例: SADD batch_id:1111:user_id 1001

3.6 Coupon - Use 使用

When to check if the user can use this coupon?

• Checkout Page [x]
• Shopping Cart

On the Checkout Page, verify the coupon:

• Check if expired 是否过期
• Check if could use for the product 是否能用于该商品
• Check if the usage amount is met 是否满足使用金额
• Check if coupons are overlapping 是否重叠/互斥

每个用户可能会拥有很多张优惠券，先查询并返回本次消费可以使用的优惠券。

SELECT batch_id FROM coupon WHERE user_id = 1001 AND status = 0;

SELECT rule_id FROM coupon_batch WHERE batch_id = 1111;

SELECT name, type, rule_content FROM rule WHERE rule_id = 1010;

Challenge - Consistency Issue in Distributed Systems

Solution 1: Distributed Transaction Pattern: TCC

• Use a coupon operation record table to record if the operation of each stage is successful. 使用优惠券操作记录表，记录每个阶段的操作是否成功。

create table t_coupon_opt_record
(
    user_id     int      null comment '用户id',
    coupon_id   int      null comment '优惠券id',
    operating   int      null comment '操作，0-locked、1-used、2-available',
    operated_at datetime null comment '操作时间'
);

• Try-Confirm-Cancel, 分布式事务主流解决方案之一
• Step1. Try
  • 对资源进行冻结，预留业务资源
  • 创建订单时，将优惠券状态改为 "冻结"
• Step2. Confirm
  • 真正执行业务逻辑
  • 订单创建成功后，将优惠券状态改为 "已使用"
• Step3. Cancel
  • 释放 Try 阶段预留的业务资源
  • 订单创建失败后，将优惠券状态改为 "未使用"

Step 4. Scale 扩展

4.1 Challenge - Expiring Coupon Reminder 过期券提醒

• Solution 1: Scheduling Task
  • 定时扫券表
  • 扫描数据量太大，随着历史数据越来越多，会影响线上主业务，最终导致慢SQL。
• Solution 2: Setup Time-To-Live
  • 失效时间
  • 有些券的有效时间太长了(30天)以上，有可能造成大量 MQ 积压
• Solution 3: Use a Notify table to record
  • 使用通知表去记录
  • 扫描的数据量小，效率高。删除无用的已通知的数据记录

The design of notify table 通知表设计

create table t_notify_msg
(
    id          bigint auto_increment comment '自增主键',
    coupon_id   bigint       null comment '券id',
    user_id     bigint       null comment '用户id',
    notify_day  varchar(255) null comment '需要执行通知的日期',
    notify_type int          null comment '通知类型，1-过期提醒',
    notif_time  timestamp    null comment '通知的时间，在该时间戳所在天内通知',
    status      int          null comment '通知状态，0-初始状态、1-成功、2-失败',
    constraint t_notify_msg_id_uindex
        unique (id)
);

alter table t_notify_msg
    add primary key (id);

Coupons' Time-To-Live Reminder Process 优惠券过期时间提醒:

1. Insert the reminder records into the notify_msg table when creating coupons. 创建优惠券时，插入提醒记录到通知表中。
2. Use userId + batchId + notifyDate as the unique index, to avoid duplicate record notifications in the same batch, and ensure that you will only be notified once a day. 防止同一个批次有重复的记录通知，保证每天只会被通知一次。
3. Create notify_time, as index, and the daily notification scan is queried through this index column. 建立通知时间索引，每日的通知扫描该列。
4. After the notification is completed, delete the data through the scheduled task. 通知完成后，通过定时任务删除数据。

4.2 API Traffic Limiting

• Frontend traffic limiting
  • 点击一次后，按钮短时间内置灰
  • 防止重复点击
• Backend traffic limiting
  • 部分请求直接跳转到繁忙页
  • 参考其他常规限流方法