I have two different databases. One of these is a Users table, and another is a Groups table.

A group can have multiple users, and a user can have multiple groups

What's the best way to associate a user with a group? I see some options:

  • Store a list of users in the Group and store no list of groups for the user.
  • Store a list of groups in the User and store no list of users for the group.
  • Store both a list of users for each Group, as well as a list of groups for each user.

What are the pros and cons of each design? I think the 3rd option is the worst as it requires synchronization work, and duplicates data.

  • Even though this smells like homework... the best way is to have a UserGroup table that has UserID/GroupID as a link table. You have a many-to-many situation, creating an intermediate table to store the relationship resolves the situation by "reversing the crows feet" on your ERD. – blobbles Jun 6 '17 at 4:58
  • > "Even though this smells like homework". It's not – H Bellamy Jun 6 '17 at 5:01
  • If you have the option of changing technology a graph DBMS would excel at this kind of thing. – Michael Green Jun 6 '17 at 10:15
  • 2
    If you're looking for a NoSQL approach edit the question to make that very clear. – Michael Green Jun 6 '17 at 10:18

A group can have multiple users, and a user can have multiple groups

It's called a many-to-many relationship. Relationally, there is only one way to store that and that's with a linking-table,

CREATE TABLE users ( userid int PRIMARY KEY, name_user text );
CREATE TABLE groups ( groupid int PRIMARY KEY, name_group text );
CREATE TABLE user_group (
  userid  int REFERENCES users,
  groupid int REFERENCES groups,
  PRIMARY KEY (userid, groupid)

Any other way you store that you'll lose relational integrity.

| improve this answer | |
  • Thanks - this looks good for SQL. What about a NoSQL database like Elasticsearch? – H Bellamy Jun 6 '17 at 6:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.