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)