Rethink system design in a much fun way, as a former urban planner/landscape planner. Take Coupon System as example

• Coupon System
• what&rsquo;s is the <strong>scenario</strong> planning of this project
  • demand analysis
• Service
• storage
  • create schema
  • create coupon
    • create coupon rule
    • create coupon-batch
  • send coupon
    • data flow when users are no more than 10 k
      • touch system
    • data flow when users are more than thousands
      • data flow in asynchronous
      • touch system
    • touch system when users are more than 10 Million
    • transaction
  • receive coupon
    • high concurrency cause database crash
    • Redis data verification
• redeem coupon
  • Confirm the order page and verify the coupon
  • stage and system
  • Try-Confirm-Cancel
• Scale
  • Expiring coupon reminder
  • Database level optimization
  • send coupon interface current limit protection
  • use Cache Aside to reduce response time
  • use geolocation to reduce response time
  • Sharding Key
  • multi region

Problem-based learning to understand Coupon System.

1.How to send coupons in bulk (100, 100k, 1million clients)

2.How to prevent coupon oversale

3.How to set up a threshold discount

4.How to prevent users from repeatedly claiming coupons

Coupon System

1. what’s is the scenario planning of this project

1.1 Send Coupons

• The operation of sending discount coupons is an asynchronous or synchronous operation?

1.2 Receive coupons

• specific groups of customers or every customers can get those coupons?

• How many identical coupons can a user get?

• Apply Coupons automatically or need customer’s request for the coupon codes?

1.3 redeeming coupons

• requirements for redeeming a coupon code

demand analysis

retailers ：

• create coupon
• send coupon

customers :

• receive coupon
• place order
• redeem coupon
• purchase

2 Service

Gateway

• Payment Service
  • Payment DB
• Order Service
  • Order DB
• Coupon Service
  • Coupon DB
• Touch Service

storage

assume:

coupon_batch

create a coupon_batch, send a conpon to a customer, serve to users are within a reasonable price range to help them meet the threshold for the discount.

For example, if the in-cart value threshold to qualify for a discount is $100, can get a $10 off.

create schema

• coupon_batch Table

column          type        explain         example
batch_id        INTEGER     batch ID         black friday
batch_name      INTEGER     batch Name        "bb"
coupon_name     VARCHAR     coupon Name      "aa"
rule_id         INTEGER     rule Id          1010
total_count     INTEGER     total count      10000
assign_count    INTEGER     assign count     5000
used_count      INTEGER     used count       2500   

• rule Table

column          type        explain         example
rule_id         INTEGER 
name            VARCHAR 
type            INTEGER 
rule_content    BLOB                         // JSON

• rule content

'{
    threshold: 5
    amount: 5
    use_range: 3 
    commodity_id: 10 
    receive_count: 1 
    is_mutex: true 
    receive_started_at: 2020-11-1 00:08:00 
    receive_ended_at: 2020-11-6 00:08:00 
    use_started_at: 2020-11-1 00:00:00 
    use_ended_at: 2020-11-11 11:59:59
    }'

• coupon table

create coupon

create coupon rule

INSERT INTO rule(name, type, rule_content) VALUES("rule", 0, 10000);

create coupon-batch

INSERT INTO coupon-batch(coupon_name, rule_id, total_count) VALUES("aa", 1125, '{
    threshold: 100 
    amount : 10
    ......
    }');

send coupon

data flow when users are no more than 10 k

touch system

touch system

• text
• mail
• station letter

message table

column          type        explain         example
id              INTEGER     
send_id         INTEGER     
rec_id          INTEGER     
content         VARCHAR     
is_read         INTEGER     
send_time       DATETIME     

assume stores want to send message to their clients, traverse the user table first, then insert the station messages into the message table of each users.

If there are 100 users, a group message will need 100 insert operations

how about there more than thousands users in this user table, send station letter once would take up 10 MB space?

data flow when users are more than thousands

data flow in asynchronous

message oriented middleware: when management server would sent request 100 k per second, and coupon server can only receive 10 k request, use message oriented middleware.

touch system

seperate one table into two tables, compared to insert 10 k operation, it only store once

message table

column          type        explain         example
id              INTEGER     
send_id         INTEGER     
rec_id          INTEGER     
message_id      INTEGER     foreigner id     
is_read         INTEGER      

message_content table

column          type        explain         example
id              INTEGER     
contend         varchar     
send_time       DATETIME     

station letter steps:

• insert into message_content
• insert a record in every user’s message table to show there is a sation letter

how about there more than 10 Million users in this user table

touch system when users are more than 10 Million

there will be only 20% active users, so insert the data to those active users

message table

column          type        explain         example

id              INTEGER     formation id    11
rec_id          INTEGER     
message_id      INTEGER     foreigner id     
is_read         INTEGER      

message_content table

column          type        explain                 example
id              INTEGER     message content id      11
send_id         INTEGER     
contend         varchar     
send_time       DATETIME     

station letter steps:

• insert text into message_content, don’t insert the record in every user’s message table
• after users log in, first query those in message_content but no recorded data in the message, which indicating that it is an unread station letter. When checking the content of the letter, inserting the relevant records into the into message table (it requires users log in)

how to solve coupon oversale leading to Excessive load problem

transaction

1. The operation provides user files that meet the conditions, upload them to the coupon issuance management background and select the ones to be sent.

2. The management server generates a message according to the user ID and coupon batch ID, and sends it to the message middleware

3. Coupon server receive message

INSERT INTO coupon(user_id, coupon_id, batch_id) VALUES(11, XX, 23);

UPDATE coupon_batch SET total_count=total_count - 1, assign_count = assign_count + 1 WHERE batch_id = 11 AND total_count > 0;

Each transaction must succeed or fail as a complete unit

receive coupon

1. Check coupon balance

SELECT total_count FROM coupon_batch WHERE batch_id = 11;

2. Added coupon user table to deduct the balance

INSERT INTO coupon(user_id, coupon_id, batch_id) VALUES(11, XX, 23);

UPDATE coupon_batch SET total_count=total_count - 1, assign_count = assign_count + 1 WHERE batch_id = 11 AND total_count > 0;

Each transaction must succeed or fail as a complete unit

high concurrency cause database crash

It would actually be situations similar to flash sale.

four step to slove those problem:

• 1. high concurrency cause database crash
• 2. cache warm up
• 3. under high concurrency Large amount of cache release crash the database
• 4. message queue deal with it asynchronous

How to prevent users from repeatedly claiming more coupon?

Redis data verification

1. check Cache before claim coupon SISMEMBER KEY VALUE

SISMEMBER batch_id:11:user_id aa

2. claim coupon

3. update Cache SADD KEY VALUE1…VALUEN

SADD batch_id:11:user_id aa

redeem coupon

Confirm the order page and verify the coupon

1. whether it has expired
2. the scope of application
3. whether the threshold is reached
4. if mutually exclusive

return available coupon

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

SELECT rule_id FROM coupon_batch WHERE batch_id = aa;

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

choose available coupon, return the result

stage and system

• confirm order – coupon system
• submit – coupon system + order system
• pay – coupon system + order system + payment system

use Distributed Transaction Try-Confirm-Cancel

TCC is a compensating transaction pattern for business model that is two-phased. try phase puts a service in pending state. For example, a try request in our flight booking service will reserve a seat for the customer and insert a customer reservation record with reserved state into database.

Coupon_opt_record Table

column          type        explain                         example
user_id         INTEGER     user id                             10
coupon_id       INTEGER     coupon id                           11
operating       INTEGER     0--lock, 1--cancel, 2--unlock       0
operated_at     DATETIME    operate time                        2022-11-01 00:01:20

Try-Confirm-Cancel

• Try

  • try phase puts a service in pending state. For example, a try request in our flight booking service will reserve a seat for the customer and insert a customer reservation record with reserved state into database. If any service fails to make a reservation or times out, the coordinator will send a cancel request in the next phase

• confirm

  • moves the service to confirmed state. A confirm request will confirm that a seat is booked for the customer and he or she will be billed. The customer reservation record in database will be updated to confirmed state. If any service fails to confirm or times out, the coordinator will either retry confirmation until success or involve manual intervention after it retries a certain number of times.

• Cance

  • In case of payment failure/, or in the case of payment failure, cancel the order to execute the business operation, cancel the business resource of executing the order, change the coupon status to “unused”

Scale

Expiring coupon reminder

• 1. Scan the coupon table regularly.

disadvantages: a Scan operation on large tableS takeS an excessive amount of time and cause performance problems.

• 2. delayed messages

disadvantages: Some coupons are valid for too long (30 days) or more, cause a serious message backlog in the current queue

• 3. Add a notification table

Advantages: small amount of scanned data and high efficiency, delete is_read records.

Notify_msg Tab

column              type                explain                 example
id                  INTEGER             auto-increme            1100
coupon_id           BIGINT              
user_id             BIGINT 
notify_day          VARCHAR 
notify_type         INTEGER 
notify_time         TIMESTAMP 
status              INTEGER 

steps:

1. hen creating a coupon, insert the record into notify_msg
2. Set user ID + batch ID + notification date as a unique index to prevent duplicates record notifications.
3. Establish notify_time index, queried through this index column to improve query efficiency
4. After the notification is completed, delete the data

Database level optimization

batch_table index : batch_id

coupon table index: coupon_id + user_id + batch_id + order_id

send coupon interface current limit protection

frontend current limiting

• after clicking, the buttom becomes gray

backend current limiting

• some request would lead to busy page

use Cache Aside to reduce response time

data in Cache

• long to short (generate new short url)
• short to long (query short url)

use geolocation to reduce response time

Optimize server access speed • Different regions, use different web servers • Resolve users in different regions to different servers through DNS

Optimize data access speed • Use Centralized MySQL+Distributed Memcached • One MySQL with multiple Memcached, Memcached distributed across regions

how about it is not weibo but twitter, DAO is ten times larger then weibo?

how to scale with high QPS problem

Vertical Sharding vs Horizontal Sharding

Vertical sharding :Vertical partitioning involves creating tables with fewer columns and using additional tables to store the remaining columns

Tiny URL only has two column, it doesn’t need vertical Sharding here

Sharding Key

• one Long url mappting to multiple short url

  • use Cache store all Lont to Short
  • create short url for one long url, if Cache Miss happens, just create new short url

• one Long url mappting to one short url

  • use ramdon to generate Short Url
    • two table, one is Long to Short, one is Short to Long
  • base 62
    • Relational database only support auto increase id in one mathine

• base62 sharding key
  • Hash(long_url) % 62 as Sharding key, put in short url column

old short key: AB1234

now short key: Hash(long_url) % 62 + AB1234

cons: the number of machine is not more than 62

multi region

DB USE, UB CHINA

To create a custom URL

random

shortKey                longUrl
a9exBl                  http://wwww.rileyshen.com/

just put custom url into shortkey

custom_url                longUrl
aa                  http://wwww.rileyshen.com/

base62

shortKey                longUrl
a9exBl                  http://wwww.rileyshen.com/

shortKey                longUrl                         customUrl
a9exBl                  http://wwww.rileyshen.com/         xxx

adding a new column is not recommended, it will waste space.

• create a table store custom_url

• CustomURLTable

custom_url                longUrl(index=true)
aa                  http://wwww.rileyshen.com/

• create custom url (query and insert in CustomURLTable)
• create short url by long url
  • check whether CustomURLTable exist or not
  • query and insert in URLTable

review

1.How to send coupons in bulk (100, 100k, 1million clients)

• sending coupons

100 – syn, touch system(1 table) 100 k – asyn, Message Oriented Middleware, touch system(2 table) 1 million– asyn, Message Oriented Middleware, touch system(active users, need log in, search in messate content table first, then insert the message into message table)

2.How to prevent coupon oversale

• scenario：function need here

• demand analysis：QPS and Storage

• service：UrlService

• data anylysis：SQL vs NoSQL

• data anylysis：schema design

• Work Solution

• Improve access efficiency between web server and data server

• Using caching to improve the efficiency of read requests

• Improve access efficiency between users and servers

• Solved the problem of slow access for Chinese users to US servers