0

Have a requirement where promotions table needs to be created which can have multiple promotion types. i.e Coupons, Promo Codes, Gifts etc

The problem is when creating a Promotion how can we save the foreign key value of promotions types where every promotion type is a separate entity and has it's own attributes.

Resolutions in my mind:

  1. Create separate tables for every promotion types to accommodate the relation between Promotions and relevant Type table eg: Promotion_Coupon_Relation

  2. Drop foreign Key constraint in promotion table and create a column that will store the foreign key value every time the promotion is created based on a type. But in this case the relation will not be concrete and will be identified based on the promotion type only.

PromotionType:

PromotypeID, PromoTypeDesc (eg: Coupon,PromoCode,Gifts and can be more in future)

Promotion:

PromotionID, PromotypeID, PromotionTypeReferenceID, EffectiveDate, EndDate, Active

Coupon:

CouponID, CouponName, CouponCOde, CouponTitle, isActive

PromoCode:

PromoCodeID, PromoCodeName, PromoCodeText, PromoCodeTitle, isActive

Gift:

GiftID, GiftTitle, GiftDesc, isActive

please advise.

Umar Malik
  • 63
  • 4
  • 10
  • Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Oct 03 '18 at 13:05
  • Hi. This is a faq. Please always google many clear, concise & specificversions/phrasings of your question/problem/goal with & without your particularstrings/names & read many answers. Add relevant keywords you discover to yoursearches. If you don't find an answer then post, using 1 variant search as title &keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on [mcve]. – philipxy Oct 03 '18 at 13:06

2 Answers2

1

I see two solutions

General tables

All promotion types are in a single table. But since the details of each promotion type is variable, you must create generic columns to store such attributes. hum, this would be a great place for an XML file...

  • Table Promotion: promoid, title, ...
  • Table PromotionType: promotypeid, name, attribute1name attribute1value, attribute2name, attribute2value ..., attributenname, attributenname
  • Table Promotion-Type-Relation: promotypeid, promoid

If only one type can be linked to a promotion, no need for the Promotion-Type-Relation table. Just add promotypeid as a foreing key in Promotion.

Specific tables

Each promotion type gets a table.

  • Table Promotion: promoid, title, ...
  • Table Gift: giftid, name, ...
  • Table Coupon: couponid, name, ...
  • Table Promotion-Gift-Relation: promotypeid, giftid
  • Table Promotion-Coupon-Relation: promotypeid, couponid

Again link tables are nor required if you only allow 1 of each type.

Discussion

The General tables method is simpler at the database level, but can become hell in the code. Lets say Gift and Coupon both have an expiration date, you would setup the name of an attribute as that date and the value at something. Now to query on that date, you have to go through the attribute names.

Nic3500
  • 8,144
  • 10
  • 29
  • 40
  • Specific tables would be a mess once we have more promotion types coming in the future and i would hesitate going the xml way. Need to brainstorm some other way to keep the relationship intact among entities. – Umar Malik Oct 03 '18 at 13:47
  • 1
    If you go with a generic model, make sure you create indexes on the attribute names to ensure queries performance is optimal. – Nic3500 Oct 03 '18 at 18:05
0

If you are going to do this in a single line then all fields for all promotions would need to be in the table. I have done this in the past and can recommend against it. Longer term these sort of solutions become very unwieldy.

Other options are to store Meta data / Key value pairs, so a line would contain data similar to:

  • Primary Key
  • Foreign key to PromotionType
  • Key type e.g. "PromoType", "CouponName","PromoCodeText"
  • Associated Value e.g. "BoGoF", "M&S Offer", "Buy one get one free"

These can the be queried and pivoted or returned as XML etc. However there is always a hit in using and rendering Meta data. May be worth considering where the data is going next and outputing as preformated JSON or XML for further use.

Matthew Baker
  • 2,637
  • 4
  • 24
  • 49
  • That would be a mess and i don't want to go with xml any more as we already have loads of xml relational data. Need a way to go with this using concrete sql dependency. – Umar Malik Oct 03 '18 at 12:42
  • I had a similar scenario before where i had to drop the relation and store multiple tables foreign keys in one single column and then query it based on the typeid which would tell us corresponding table to query from. – Umar Malik Oct 03 '18 at 12:44
  • You can allow multiple NULLable foreign keys which are enforcable. e.g. fk_Promotion_id, fk_Coupon_id, fk_PromoCodeID. Then link to the various tables. None of these are great options, but I think that may be closer to what you need? – Matthew Baker Oct 03 '18 at 12:48
  • I did think about that but again in future when we shall have more promo types this would require unnecessary creation of columns. I am looking for something that could give the relationship a concrete bonding. – Umar Malik Oct 03 '18 at 13:49