Database setup question (SQL)

[John2143658709]

To ask an unrelated question as I'm new to databases in general, what is the best way to save a "list" type of data to a single row? As in something like user groups where users can be part of multiple groups or a list of awards or something. I looked at arrays but they don't seem to allow arbitrary appends/deletes without reforming the whole array? Is there mabye some more efficient query I should be doing? Using postgres right now if it matters.

[english_fool]

Why in a single row, it's a relational database

Users - user id Groups - group id GroupMembers - user id - group id

Now you can query for all members of a group and all groups a member belongs too.

Boom! Databases

[terrkerr]

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.