CODEWITHSUNDEEP
phpmysqldatabasedatabase-designoptimization
jondinham
jondinham

Reputation: 8511

How to create product table in which each product is under multiple categories in MySQL?

I have 2 MySQL tables:

categories (id int, name varchar(1000))

And 

products (id int, name varchar(1000))

Each product can be under multiple categories. I'm thinking of adding column "category_ids" into the table 'products' with category Ids separated by semicolons, but this method is inconvenient for MySQL query.

Any other possible methods?

Upvotes: 0

Views: 3343

Answers (4)

rahularyansharma
rahularyansharma

Reputation: 10755

make third table which have refernce to both table as in below image

enter image description here

Upvotes: 2

Zohaib
Zohaib

Reputation: 7116

That seems to be a many to many relationship....

In order to map many to many relationship, u will need another table

categories_products(id, category_id, product_id)

so one product can come under many categories and similarly one category can hold many products.

Product table will have one to many relationship with categories_products table Categories table will also have one to many relationip with categories_products table

thats a standard way to implement many to many relationships

Upvotes: 1

StevieG
StevieG

Reputation: 8709

Add a junction table linking the two:

**product_categories**
productid (FK ref product.id)
categoryid (FK ref categories)

Upvotes: 2

Pr0no
Pr0no

Reputation: 4099

Create a table that matches products with categories:

product_id category_id
1          1
1          2
2          5
3          5
3          2

etc. Hope it helps :)

Upvotes: 5

Related Questions