Clever Geek Handbook

How can I prevent someone from using a promo code twice?

I have a database that looks like this:

database

When a user wants to use a promo code, he first checks if used_promo is null, which means the user has not used a promo code before. (User can only use 1 promo code)

I can do something like this

SELECT used_promo FROM users WHERE id=5

then I can check from PHP if used_promo is null and if so I can do

UPDATE users SET used_promo=2, balance=balance+100 WHERE id=5

Is it possible that a user can try to use a promo code multiple times at the same time, SELECT will return used_promo to be null until it is set to 2 and the balance is added multiple times?

+3


source to share


3 answers


In the scenario you described, you could theoretically use a promo code multiple times. Use a single query that will return an ID if the update is successful or no rows otherwise.



UPDATE users 
SET used_promo=2, balance=balance+100 
WHERE id=5 AND used_promo is null
RETURNING id;
+1


source


Create another relational table "promo_used" with columns:

  • ID
  • user_id
  • promo_code_id
  • Time stamp


As soon as the user applies the promo code, this table will have a new entry. So, if the user tries to apply the same promo code again, we can check by searching this table.

Hope it helps.

+1


source


As you can imagine, you are concerned that the user might use the promo code multiple times before updating the used_promo column. I don't know what your business model is. However, this is possible in a scenario like this:

The user enters a promo code. You reduce the price of the item accordingly, but you cannot update the used_promo field because the purchase is incomplete. Without completing the purchase, the user enters a promo code for another product and gets the discount again. And after that, the user finishes purchases several times. Since you do not validate the code in the last step, the user can get all the discounts.

So

  • Maintaining the status of the promo code
  • Always check correctness

  • Use an atomic way to update your validity status, used_promo and complete your purchase. (Transactions)

    promo code and used_promo before completing purchase

0


source






More articles:


All Articles