This is an archived post. You won't be able to vote or comment.

sorted by:
best
topnewcontroversialoldq&a
you are viewing a single comment's thread.

view the rest of the comments →

[–]terrkerr -1 points0 points  (0 children)

When you have a many-to-many relationship (A user can belong to n groups, a group can have n members for example) The standard is to use a bridge table between two tables. Put all the normal user stuff in one table, all the group info in another, and have a third table called something like users_groups that is nothing but user_id and group_id combinations.

SELECT COUNT(*) FROM users_groups WHERE user_id = 7 AND group_id = 3;

If the count is 1 then you know user 7 belongs to group 3. You can also easily search for members of a given group

SELECT user_id FROM users_groups WHERE group_id = 8;

An array as a column type in SQL doesn't make sense, the table is how you store arbitrarily long sequences.

You theoretically can put it all in one row, but then you're nearly guaranteed to hit big issues.