I must give a coupon table to my db. You will find 3 kinds of coupons : percentage, amount or two for one.

To date I have develop a coupon table that consists of these 3 fields. If there is a percentage value not set to null then it is this type of coupon.

Personally i think it isn't the best way to get it done. Must I produce a CouponType table and just how can you view it? Where can you store these values?

Any help or signal appreciated!



You are correct, I believe a CouponType table could be fit for the problem.

Two tables: Coupons and CouponTypes. Keep CouponTypeId within the Coupons table.

So to have an example, you will have a Coupon record known as "50% off", if would reference the percent off CouponType record and after that you can determine the logic to consider 50% off the price of the product.

So you can now create limitless coupons, whether it's $ 1 amount coupon type it will require the "amount" column and address it like a amount of money. Whether it's a percent off it'll address it like a percentage and when this is an "x for 1" deal, it'll treat the worthiness 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> :))

Later on you may have a lot more different coupon types. You might have different business logic connected together - who knows. It certainly is helpful to complete the items in this situation, so yes, certainly, produce a coupon type area as well as an connected dictionary table to choose it.

I'd certainly produce a CouponType research table. This way you avoid all of the NULL's and permit more coupon types later on.

Coupon coupon_id INT title VARCHAR coupon_type_id INT <- Foreign Key

CouponType coupon_type_id INT type_description VARCHAR ...

Or I guess you might have a coupon type column inside your coupon table CHAR(1)