Assigning users to more then 1 group

[thecookie]

Hey! I am looking for some tips and hints for a problem I have. Using mysql..

I have tables, a 'user' table and a 'group' table. In the user table there is a field called 'group' where the id to a group is stored. The problem is that I want the user to be assigned to more then one group id..

The solution I have come up with is to comma seperate each id.. This works but makes the db searching harder.. like, select all users assigned to specific groups...

Is there a better solution to solve this? Making it easier to select users and such.. I don't want to have duplicate users.. each user holds kinda much data..

Thanks for any help!

[crislewis]

There a few ways to skin this and it depends on the number of groups you have and the number people are likely to belong to.

One option would be record members of a group in a group/member table. So you have a member table, a group table and a link table which ties the two together.

field might be:

id (unique key, auto incremment)
group_id
member_id

There would be a record for each member/group.

You can then get all the members of a group by "select ... where group = 'group_id'"

And you can get the groups a member belongs to by "select ... where member_id = 'member_id'"

To maintain the table simply insert or delete records as members subscribe/unsubscribe to the different groups.

Another option(if there a only a few groups) would be to code in the groups in the member record using the old binary flags technique. e.g. where there a 4 groups a value of 2 (binary 0010) means the member is in group 2 and a value of 9 (binary 1001) means they are a member of both 1 and 4. Each binary digit represents a group. You might also like to check out the SET field type in MYSQL which will handle this for you. You can then use masks to get members of any combination of groups.

I favour the first option - although it gives another table to maintain it does mean your relationships are more clear.