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?

  • insert and return an autoinc counter? realisticaly you would just turn off the check – Ewan May 13 '19 at 18:49
  • How will such a counter-constrained coupon go down is real life? "Sorry sir, you don't get your discount. You are user 100,001 of this coupon and we accept the code only 100,000 times. <user walks off and writes a scathing review on social media>" – Bart van Ingen Schenau May 14 '19 at 11:00
  • @BartvanIngenSchenau Using counter constrained is one way of doing it - but there has to be a constraint. One option is to use time based constraint, but it is tricky as we have no control over how many orders are being processed within the specified time. Not sure if the user would feel that bad - I believe it is pretty common for coupons to expire. – rdev May 14 '19 at 14:55
  • @rdev: With a time constraint (or a count constraint of 1, i.e. a personal code) I know as a user if I meet the criteria for the coupon or not. With a different count, I can't know if I will be eligible because I can't know how many customers before me used the coupon. – Bart van Ingen Schenau May 14 '19 at 15:41

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.