CODEWITHSUNDEEP
mysql
user1091856
user1091856

Reputation: 3158

MySQL: 'Product' can belong to multiple groups?

In my MySQL products table there is a column called groups which products can belong to. What is actually stored in this column are id's of another tabled called groups.

What if I want to select products that belong to a specific group?

If a product can only belong to a single group, I could simply use

where group={groupid}

But what if a product can belong to multiple groups?

I could store them like this: '4,8,10,42', each of these numbers meaning a group id. But then how would I query a select?

Upvotes: 3

Views: 442

Answers (3)

Mark Byers
Mark Byers

Reputation: 837946

I could store them like this: '4,8,10,42',

No, please do not do that!

There is a completely well-known, standard, efficient and well tested best practice for creating many-to-many relationships, called a join table. Here's how to represent that product 1 is a member of groups 4, 8, 10 and 42 using a join table:

product_group
product    group
1          4
1          8
1          10
1          42

Upvotes: 2

Hawili
Hawili

Reputation: 1659

Database structure is the one you have to watch in here if you want a product to have multiple groups, you have to create a third table that products and groups connect to. Then all you have to do is put the product id mapped to the group id

In case the product belongs to only one group it will have only one entry in this table, but if it belongs to multiple groups then it will have (eventually) multiple entries

Upvotes: 2

lanzz
lanzz

Reputation: 43158

You should not store a list of products in a single field. Instead of a column products.groups, you should have a separate table product_groups (product_id, group_id), where you store each association between a product and a group. Then you can select all products from a group as this:

SELECT product.*
FROM product_groups
INNER JOIN products ON (products.id = product_groups.product_id)
WHERE product_groups.group_id = :group_id`

Upvotes: 3

Related Questions