What is the best way to store one-to-many or many-to-many relationships in PostgreSQL?

Question

I am currently integrating an open source chat (AJAX Chat) into another project. Now, the chat by default just gets its valid users and valid channels from a file, but obviously this is not ideal when you have a database with constantly changing users.

So, I want to make it so that the chat loads user and channel info straight from the database. I was thinking that the design should be as follows(If you feel differently, please do let me know):

• We have some chat channels (public, marketing, etc...)
• Then we have groups assigned to channels (like PR team 1, IT people, etc...)
• Then we have users who are part of groups AND in some cases are assigned directly to the channels.

I was thinking of implementing the above with tables like these:

Channels Table:

|----|Channel_Name||Channel_ID||Groups_Assigned||Users_Assigned|----|  
|----|---Public---||-----0----||---1,2,3,4,5---||-----3,4------|----|  
.  
.  
.etc...

Note: The groups assigned table contains the group id's of the groups assigned to the channel, while the users assigned contains the id of the users who are not part of the groups that are assigned.

The Groups Table:

|----|Group_Name||Group_ID||Users_Assigned|----|  
|----|---Team1--||----0---||------5,10----|----|  
.  
.  
.etc...  

Sorry for the badly drawn tables.

Now, with the above implementation, when a user logs in, the program is going to get the user id (from the users table), then search the groups table for all groups that contain the user id, and finally, search the channel table for all channels that contain either groups (that a user is part of) or channels that have the user directly assigned to them.

My idea is possible, but it seems a bit, uhh, inefficient. Since I would have to be storing the assigned ids (both group and user) in the format 1,2,3...., I would have to use either PHP's explode() or some other PostgreSQL function that can search strings. I would most likely be storing an array of groups, and then cycling through them all, one row at a time, this seems really slow to me.

Or, I could have a boolean column for each user, but that would result in way too many columns, and I don't want to create a new column every time a user is created.

So, how would you guys do this? And, if for some mad reason, you happen to agree with my initial idea, then could you help me figure out how to actually write the code to actually do it.

Thank you for your time, have a good day.

Answer 1

Yes, it's inefficient to store comma-separated strings of numbers and to try to search your database for a given number. For more on this, see my answer to Is storing a comma separated list in a database column really that bad?

Instead, you should use intersection tables to store the many-to-many relationship between users and groups, and between groups and channels. Then your searches will benefit from indexes, and you can use a join to get back to the group or channel table.