1

I'm creating a database for a department store and the database has two tables: Items and Coupons. An item, like a TV, can only ever have one coupon. Some items don't have a coupon at a given time. Therefore my schema is:

Items: item_id, name, price, desc, ...
Coupons: coupon_id, item_id, rate, ...

This way, when I query for items, I get a list of all items. If I want only items with a coupon, I can either select all coupons and then individually query for the item to which a coupon belongs, or I can perform a natural join between the two tables and get a list of only items with coupons.

I don't think adding a coupon_id column to the Items table is correct because then there will be null coupon_ids, which I believe is not optimal.

Is everything I've said above correct? Or would adding the coupon_id column to Items and having null coupon_id be completely acceptable?

1

I would favor your first arrangement where you have an Item_Id FK in the Coupon table.

  • You will be able to enforce the "One coupon per item rule" through a unique constraint on the Item_Id column in your Coupon table.
  • You will be able to easily allow multiple coupons per item if business decides to change their mind.
  • Like you said you can easily select and filter your items vs. coupons relationships
  • Putting the Item_Id in the Coupon table allows you to maintain a history of coupons that were valid for that item which is often useful.

The only potential problem I see is in some ways you are focusing on tying a coupon to a specific item on issuance of the coupon. This only becomes a problem when business decides that they want to hand out "20% off your highest priced item," or "50% off everything in the store on XYZ date" coupons to their most valuable customers. This can be resolved of course. I just mentioned it to get you thinking about ways the design might need to change in the future.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.