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

database-designrelational-theoryschema

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?

Best Answer

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.

Related Solutions

How to store several lists and their items

It sounds like you want three tables

PROJECT( project_id, <<other attributes>>)
LIST( list_id, project_id, <<other attributes (e.g. the name of the list)>> )
LIST_ITEMS( list_item_id, list_id, <<other attributes>>)

Combining the LIST and LIST_ITEM tables wouldn't be appropriate because then you'd be storing attributes of the list (i.e. the list's name and description) in multiple rows of the LIST_ITEM table rather than just once in a single row of the LIST table.

Mysql – Creating schema for a shop that sells items from multiple tables

I recommend reading the following link on Database Inheritance: http://blogs.microsoft.co.il/blogs/bursteg/archive/2007/09/30/how-to-model-inheritance-in-databases.aspx

Databases don't naturally fit hierarchies - they do work, but it's not their strong suit.

In your case I would change shops_and_items for

**products**(product_id (PK), shop_ID (FK), ... then columns for common fields: name, price, stock, delivery time)

Then...

Option 1 :: Fewer Product Categories

If you only intend to have furniture and clothing then you could do

**clothes**(product_ID(FK and PK), size, colour, gender etc)
**furniture**(product_ID(FK and PK), height, width, depth, material etc)

Note that product_ID is a foreign key and primary key... a product would either have a corresponding record in clothes OR furniture. This doesn't need to be auto incrementing. A product has one clothes item or one furniture item (not both).

Option 2 :: Many Product Categories

If you intend to expand to lots of different products (electrical, food, ) and possibly sub-products then you will have a lot of tables and it will be a mess to manage! In this case it's easier to go down a meta-data route like WordPress http://codex.wordpress.org/images/9/9e/WP3.0-ERD.png

WordPress uses wp_posts for a post, page, any many other plugin-defined types (map, questionnaire, gallery etc), if they need extra properties then they are stored in wp_postmeta.

You would keep the products table but add on:

**product_meta**(product_id(FK), meta_name, meta_value)

e.g.
4 (Surfer T-Shirt), colour, white
4, size, Medium
4, gender, male
18 (TV Stand), material, metal
18, height 40cm
18, depth 33cm
18, width 60cm

This might need some more validation before the data goes into the database as meta_value will most likely be a varchar column so you will need to standardise how you interpret values. Will your code use M/F for Male/Female? What units for measurments?

I recommend grabbing some open source shopping carts and installing them just to find out how they structure their SQL code - they will have their own solutions for sub-categories and custom properties of products.