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

MySQLschema

I am trying to create a table in mySQl that contains a list of all the items that a shops. For example:

shops (shop_ID (PK), shop_name)

shops_and_items (shop_ID (PK), item_ID (PK), price)

The issue I'm facing is that a shop can sell two types of products. For example, furniture and clothing. In my database, I already have separate tables for both furniture items and clothing items. The suggested shops_and_items schema therefore does not work.

I'm wondering, what is the best way to go about it. Should I create two tables shop_and_furniture_items and shop_and_clothing_items ?

Best Answer

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.

Related Question