Multiple categories to a single product

0 Harshit Punn · July 2, 2014
I am creating a eCommerce and i am trying to add more than 1 category to a single product .  I am having  tables having name categories and another table products. The categories table have category_id and category _title. The product table has product_id,product_title and category_id. I want to add multiple category_ids to a single product

Post a Reply


Oldest  Newest  Rating
0 Justin Hamilton · July 22, 2014
You could build a relationship between the tables. Add the product id to the category id, then make category a child of product on c.pID=p.pID

+1 Eugene Botma · July 22, 2014
the normalized approach to this would be to use a bridge table for a many - many relationship.

if your tables look like the following:

category_id | category_title

1           | Fruit
2           | Food
3           | Drink
4           | Carbonated drink


product_id   | Product_title

1            | apple
2            | bread
3           | water
4           | coke

the bridge table will look like:

product_id | category_id

1          | 1
1          | 2
2          | 2
3         | 3
4          | 3
4          | 4

where you use a combined primary key with both columns.

this table shows that 
apple falls under fruit and food,
bread falls under food,
water falls under drink
coke falls under drink and carbonated drink.

to get all products in category 2, food:
select product.* from product, product_category_bridge where product.product_id = product_category_bridge.product_id and product_category_bridge.category_id=2

there are better ways to do this (joins etc, but this should work)

to get all categories for a certain product, just reverse the previous query:
select category.* from category, product_category_bridge where category.category_id = product_category_bridge.category_id and product_category_bridge.product_id=2
0 Justin Hamilton · July 23, 2014
That will work Eugene but the Title states a one to many relationship. If that is the case, the best strategy (imo) would be to add the parent id to the child. It's one extra row opposed to one extra table and two extra rows. 

Either way should work tho.
+1 Eugene Botma · July 23, 2014
True, and the answer you posted is correct, but in the context of the question, he will need a many to many relationship, because what happens when he has a lot of items that share categories? with a one - many relationship, he will need to duplicate the category data in the table. 
  • 1

SQL & Databases


Everything SQL and Databases related in here!

Bucky Roberts Administrator