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

Question

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

So, I want to make the chat load user and channel information directly from the database. I thought the design should be as follows (if you feel different, please let me know):

• We have several chat channels (public, marketing, etc.).
• Then we have groups assigned to the channels (for example, PR 1 team, IT specialists, etc.).
• Then we have users who are part of groups, and in some cases directly linked to channels.

I was thinking about implementing the above tables:

Channel table:

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

Note. The assigned groups table contains the group identifier for the groups assigned to the channel, while the assigned users contain the identifier of users who are not part of the assigned groups.

Group table:

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

Sorry for the badly crossed out tables.

Now, when the above implementation, when the user logs in, the program will get the user ID (from the user table), then search the group table for all groups containing the user ID, and finally search the channel table for all channels that contain either groups (which are part of the user), or channels that have the user directly assigned to them.

My idea is possible, but it seems to be a little inefficient. Since I would need to store the assigned identifiers (both groups and users) in the format 1,2,3.... , I would have to use either PHP explode() or some other PostgreSQL function that can search for strings . Most likely, I will store an array of groups, and then cyclically move them one line at a time, this seems to me very slow.

Or I may have a logical column for each user, but this will result in too many columns, and I don't want to create a new column every time the user is created.

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

Thanks for your time, had a good day.

Answer 1

Yes, it is inefficient to store comma-separated strings of numbers and try to search the database for a given number. For more on this, see my answer to Does a comma separated list in a database column really do that bad?

Instead, you should use intersection tables to store many-to-many relationships between users and groups, as well as between groups and channels. Then your searches will be useful from the indexes, and you can use the join to return to the table of groups or channels.

Answer 2

I would choose another table instead of the values 1,2,3,4,5 , since they are difficult to read. Delete Groups_Assigned from the channels table and place it in a separate table in the format from 1 to a large number:

 Channel_id Group_id ---------- -------- 0 1 0 2 0 3 0 4 0 5 

I would create another table in the form of groups that would join this table here and would contain information about what group_id . Then it’s the question of writing queries that can read this design as needed.

Answer 3

One possible solution:

 Channel ------------ Channel_Id Channel_Name PRIMARY KEY (Channel_Id) 

Person and Grouping (I prefer the more User and Group , as some systems use them as keywords) can be considered as subtypes of the supertype Entity . This will help later to have only one Assignment table.

 Entity ------------ Entity_Id PRIMARY KEY (Entity_Id) Person --- ( User ) ------------ Person_Id Person_Name --- other data about persons/users PRIMARY KEY (Person_Id) FOREIGN KEY (Person_Id) REFERENCES Entity(Entity_Id) Grouping --- ( Group ) ------------ Grouping_Id Grouping_Name --- other data about groups PRIMARY KEY (Grouping_Id) FOREIGN KEY (Grouping_Id) REFERENCES Entity(Entity_Id) 

This will be used for the Person - Grouping :

 Belongs --- ( Person Belongs In Grouping ) ------------ Person_Id Grouping_Id PRIMARY KEY (Person_Id, Grouping_Id) FOREIGN KEY (Person_Id) REFERENCES Person(Person_Id) FOREIGN KEY (Grouping_Id) REFERENCES Grouping(Grouping_Id) 

And an association table for channel assignments

 Assignment ( Entity is Assigned to Channel ) ------------ Entity_Id Channel_Id PRIMARY KEY (Entity_Id, Channel_Id) FOREIGN KEY (Entity_Id) REFERENCES Entity(Entity_Id) FOREIGN KEY (Channel_Id) REFERENCES Channel(Channel_Id) 

---

You can drop an Entity table and have two association tables: one for Person to Channel and one for Group to Channel assignments.