Is this the correct way to model a relationship between an object which may or may not have another object?

Question

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?

Answer 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.