Coupon code Verification in an Ecommerce site

Question

Hi all I am implementing a coupon code verification option in my Ecommerce website. I have two types of coupons either flat amount or percentage. How to tackle both of these in backend. I mean in database schema.

Answer 1

CouponTypeTBL
id type
1  flat
2  percent

CouponTBL
id  typeid  uniquecode  amount   validfromdate   validtodate  useddate  status etc...
1   1       a1-xx       100.00   ....
2   2       cvd11       7.5      ...

So you have here a coupon:

a1-xx which is a flat amount of 100

and

cvd11 which gives a 7.5% discount.

You can make this model more complex, which currency, if flat will there be an amount left if used under the fixed amount etc .. etc..

Answer 2

I would use a table that has

CUPON CODE, PERCENT, AMOUNT, USABLE_COUNT

this way you can have cupons that do both (if someone would want that).

USABLE_COUNT for cupons that can be used only a certain number of times.

But there are a lot of other features that you might consider, like minimum pourchase amount for a cupon.

Answer 3

percentage = integer (1%, 10%, 100%, etc.) or double/float (1.11% 10.1%, 99.999%, etc.)

flat amount = integer (1, 10, 100, etc.) or double/float (1.11, 10.1, 99.999, etc.)

Save it in backend as integer or as double/float/decimal...

Answer 4

CREATE TABLE IF NOT EXISTS `coupon` (
    `code` varchar(20) NOT NULL,
    `type` varchar(10) NOT NULL,
    `amount` float NOT NULL,
    UNIQUE KEY `code` (`code`)
);

Easy example...

Answer 5

put both amount and percentage as suppurate columns. otherwise if put both percentage and amount in singe column by giving a discriminator column that will coz a problem if you want to provide a domain to either amt r percent eg: more then 100% is not allowed as discount or min 10 rs shold be given as discount.