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

Question

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?

Answer 1

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

Answer 2

Add a junction table linking the two:

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

Answer 3

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

Answer 4

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