Select exclusively a field from a table

Question

Hello,

I have to add a coupon table to my db. There are 3 types of coupons : percentage, amount or 2 for 1.

So far I've come up with a coupon table that contains these 3 fields. If there's a percentage value not set to null then it's this kind of coupon.

I feel it's not the proper way to do it. Should I create a CouponType table and how would you see it? Where would you store these values?

Any help or cue appreciated!

Thanks,

Teebot

Answer 1

You're correct, I think a CouponType table would be fit for your problem.

Two tables: Coupons and CouponTypes. Store the CouponTypeId inside the Coupons table.

So for an example, you'll have a Coupon record called "50% off", if would reference the percent off CouponType record and from there you could determine the logic to take 50% off the cost of the item.

So now you can create unlimited coupons, if it's a dollar amount coupon type it will take the "amount" column and treat it as a dollar amount. If it's a percent off it will treat it as a percentage and if it's an "x for 1" deal, it will treat the value as x.

- Table Coupons
  - ID
  - name
  - coupon_type_id # (or whatever fits your style guidelines)
  - amount # Example: 10.00 (treated as $10 off for amount type, treated as  
           # 10% for percent type or 10 for 1 with the final type) 
  - expiration_date

- Table CouponTypes
  - ID
  - type # (amount, percent, <whatever you decided to call the 2 for 1> :))

Answer 2

I would definitely create a CouponType lookup table. That way you avoid all the NULL's and allow for more coupon types in the future.

Coupon coupon_id INT name VARCHAR coupon_type_id INT <- Foreign Key

CouponType coupon_type_id INT type_description VARCHAR ...

Or I suppose you could have a coupon type column in your coupon table CHAR(1)

Answer 3

In the future you might have much more different coupon types. You could also have different business logic associated with them - you never know. It's always useful to do the things right in this case, so yes, definitely, create a coupon type field and an associated dictionary table to go with it.