CODEWITHSUNDEEP
mysqldatabase-design
oriceon
oriceon

Reputation: 379

SQL: product with multiple categories

Wich is the best database design and performance to store multiple categories (at least 3) per product? Products table could have milions of items.

To have two tables products and products_relationship (where to store product_id and category_id)?

And what about selecting products that have assigned both category 1 and 2?

  1. SELECT IN() will select 1 or 2
  2. 2 INNER JOIN per products_relationship doesn`t sound greate.

Upvotes: 2

Views: 6876

Answers (2)

juergen d
juergen d

Reputation: 204904

Your design looks good. When you have a m to n relation then you should always use such a mapping table.

You can select products that have both categories like this

select p.id, p.name
from products p
inner join product_relationship pr on p.id = pr.product_id
inner join categories c on c.id = pr.category_id
where c.name in ('cat1','cat2')
group by p.id, p.name
having count(distinct c.id) = 2

And you could name your mapping table different: product_categories to indicate what relation you build.

Upvotes: 1

sumit
sumit

Reputation: 15464

You can create many to many relationship. Google HABTM for more

Product

id name

category

id name

product_category

product_id category_id

Upvotes: 0

Related Questions