0

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?

jondinham
  • 8,271
  • 17
  • 80
  • 137

4 Answers4

5

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 :)

Pr0no
  • 3,910
  • 21
  • 74
  • 121
2

Add a junction table linking the two:

**product_categories**
productid (FK ref product.id)
categoryid (FK ref categories)
StevieG
  • 8,639
  • 23
  • 31
2

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

enter image description here

rahularyansharma
  • 11,156
  • 18
  • 79
  • 135
1

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

Zohaib
  • 7,026
  • 3
  • 26
  • 35
  • a clustered composite primary key (category_id, product_id) would better enforce integrity and be much more performant - ditch the pointless surrogate PK and read up on index types http://stackoverflow.com/questions/4419499/mysql-nosql-help-me-to-choose-the-right-one-on-a/4421601#4421601 – Jon Black Oct 28 '11 at 09:38