storing several ID's from another table in one column?

Question

Apologies if this is really stupid but I don't have any experience in php and mysql to know how things should be done. I have a customer table in a mysql db and a group table:

customers - ID name email phone group groups - ID name description

So I need to assign groups to customers if necessary, this can be more than one group to each customer. So e.g. customer 1 is in group 4,5,6

What way should I assign groups in the group column of the customer table. Should I just add the group ID's separated by commas, then just use explode when I need to get the individual ID's out?

Maybe this isn't the right approach at all, could someone enlighten me please. I would appreciate knowing the right way to do this, thanks.

D'Arcy Rittich · Accepted Answer · 2012-03-15 20:36:53Z

4

Do not store multiple IDs in one column. This is a denormalization that will make it much harder to query and change your data, as well as hurting performance.

Instead, create a separate CustomerGroup table (with CustomerID and GroupID columns), and have one row per Customer/Group relationship.

answered Mar 15, 2012 at 20:36

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mao Over a year ago

thanks, I think I already knew in some way this would make queries difficult but I was looking for the easiest (laziest) way I could think of.

Shinhan Over a year ago

Easiest way to write it now, or maintain it later? I have seen this mistake many times (putting comma delimited values in a SQL field) and never once has it failed to produce a huge problem down the line.

Shaunak · Accepted Answer · 2012-03-15 20:43:50Z

1

Here is an example of tables to show how you should implement this :

Table 1 CONSUMERS:

id name email 
1  john [email protected]
2  ray  [email protected]

Table 2 GROUPS :

id group_name   description
1  music        good music group
2  programming  programmers

Table 3 CONSUMERS_GROUPS

consumer_id  group_id
1              1
1              2
2              1

Now the table 3 is listing consumers ids which belong to which group id.

This type of relationship is called one to many relation where, one consumer can have many groups. Reverse might also be true where one group can have many consumers. In that case relationship is called many to many

answered Mar 15, 2012 at 20:43

Shaunak

18.2k5 gold badges59 silver badges86 bronze badges

Comments

Mark Byers · Accepted Answer · 2012-03-15 20:58:40Z

1

Should I just add the group ID's separated by commas, then just use explode when I need to get the individual ID's out?

No! If you do that then you won't quickly be able to (for example) query for which users there are in a specific group.

Instead use a join table with two columns, each of which has a foriegn key constraint to the corresponding table.

group_id  customer_id
4         1
5         1
6         1

edited Mar 15, 2012 at 20:58

answered Mar 15, 2012 at 20:37

Mark Byers

843k202 gold badges1.6k silver badges1.5k bronze badges

2 Comments

mao Over a year ago

thanks for the link, explains it well. I'm a bit confused though, e.g. when creating such a table I would use REFERENCES customer (ID), what happens if I try to enter a customer ID into that join table that doesn't exist, will I get an error?

Mark Byers Over a year ago

@mao: Yes, you will get an error if you try to add a value that doesn't exist in the referenced table. The row won't be inserted.

Collectives™ on Stack Overflow

storing several ID's from another table in one column?

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related