Design for an e-commerce site supporting discount coupons

Question

This is a design question asked during interviews. The requirement is to build a "discount coupon" system that tracks coupons available in an eCommerce site. Something like:

• Coupon code CDSXGF can be used 100,000 times, gives a discount of 2%
• Coupon code HFSECF can be used 500,000 times, gives a discount of 10%

Adding of coupons to the system is not an issue. The problem is maintaining "availableCouponCount" metadata when multiple users are hitting orders in the system. In lightly loaded systems, having one "availableCouponCount" field against one coupon record would work. But at higher usage, this would likely result in a bottleneck.

From a load standpoint, lets assume that we are talking about burst scenario. Sort of like Black Friday, where these coupons will get used up in 30 seconds or so.

How can this scenario be modeled effectively in MySQL?

Answer 1

Note: I'm going to answer the technical question as asked here. Depending on the role the interview is for, the correct interview answer may very well be "having a counter is stupid; there should be a time constraint or per-account limitation" (as Bart von Ingen Schenua appears to suggest in the comments).

Assumption: 500.000 isn't the exact maximum; 500.013 usages wouldn't be a problem either.

The issue with a simple UPDATE coupons SET usageCount = usageCount + 1 WHERE id = 21 is that your database will start to have locking issues, because multiple processes will want to lock the same record at the same time.

The way to prevent this is to not update coupons every time the coupon is used. Multiple strategies are available.

Log usages, periodically update Log every usage of a coupon into coupon_usages (INSERT INTO coupon_usages (coupon_id, order_id VALUES (12, 34534534)). In a different process periodically do a COUNT() over that data and use that to update coupons.

Update in a different datastore, copy over value periodically Instead of updating the counter in the database, keep the counter in something like Redis. Like before, have a different process to periodically read Redis (or whatever you decide to use) and update coupons based on that data.

Keep a counter in memory If you have an application server with persistent objects, keep a counter there. Update the database in a separate thread.

Only update some of the time Instead of increasing coupons with 1 every time the coupon is used, increase coupons with 1000 if (rand(0, 999) === 0).

That last one is a "I have no other options" option; it will give you an inaccurate count, due to the non-perfect distribution of random number generators.

These options should be enough to answer the technical side of the interview question. I wouldn't throw all of them out there at the same time. Pick something, discuss its pro's and cons, then mention another option to counter specific cons of the first option, etc.