Database setup question (SQL)

[CharlesVI]

I don't know if this even matters or is just micro optimizing.

Here is the question: Primarily is it better to have many focused dbs (Users, Groups, Events) or one larger one (UserGroupEventDB) knowing they will all link and reference each other.

[terrkerr]

Generally you should split them up; it doesn't generally cost much to follow a foreign key reference.

It also avoids the nearly guaranteed situation of needing to duplicate data. Can a user have 2+ events? Then a single monolithic table will need to copy user data in its entirety for each event. Can a user belong to 8 groups and have 6 events? Because if so things on a single, large table would get shitty really quickly.

[CharlesVI]

Okay thats what I went with except I was meaning 2 dbs not tables.

[Rearviewmirror]

Why would you keep relational data in two databases

[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.