To figure out what groups a person belongs to, you do

select group_id
  from belongs_in
 where user_id = ?
[download]

------
We are the carpenters and bricklayers of the Information Age.

The idea is a little like C++ templates, except not quite so brain-meltingly complicated. -- TheDamian, Exegesis 6

Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.

Or you could directly get the group info, which is probably what you were looking for (rather than just the id), by joining the tables:

SELECT group_foo, group_bar
FROM groups, belongs_in
WHERE belongs_in.user_id = ? AND belongs_in.group_id = groups.group_id
[download]

Makeshifts last the longest.

dragonchild gave you the right answer from the perspective of having a normalized schema, but didn't discuss performance.

If you have a Group table, indexed by group ID, and a linking table indexed by user ID + group ID, the JOIN you need to get from a user ID to a list of group names will scale fairly well. Because you have indexes, you're not scanning entire tables. Because of the composite index on the linking table, MySQL can do a partial key lookup (which avoids scanning the entire table), and can pull the group ID out of the index. From there, the lookup of the corresponding Group record is done using an index.

If you're skeptical, as you should be if you're concerned about scalability, prototype this scheme and benchmark it.

If the number of records gets big, you may need a second index on the linking table. Having the index (userID, groupID) as a primary key will indeed keep out duplicate records, and allow mysql to quickly answer the question of what all groups is user X in, because querying with a known userID, mysql can use the left column of the combined index.

But, if you want to know all the users in a given group, the linking table does NOT have an index to look up groups with. (userID, groupdID) does not contain a leftmost prefix (groupID). A table scan will be performed here. So an index on the groupID may be useful.

If your record count stays small, i'd go with dragonchilds index only. But if the numbers get big, the second index will become useful.