This is a beginner question:

A user can belong to many groups, and a group can contain many users.

Let's say the two tables look something like this:

user_id user_name user_email

group_id group_title group_description

Where and how do I store all of the users which belong to a group and all the groups a user belongs to?

I can't imagine the groups table having a users field which contains comma separated user IDs being an acceptable solution. I think I get the basic concept of relational databases, but I'm not sure of the implementation. I get the one-to-one relationship, but I'm struggling with one-to-many. Is what I'm dealing with in this example two separate one-to-many relationships?

  • This is a many-to-many relationship you have, usually implemented with an intermediate table, as JNK's answer, that has one-to-many relationships with the two other (User and Group) tables. Jul 1, 2012 at 20:32

1 Answer 1


You want three tables for this:

  • Users with a row per user and whatever details you need
  • Groups with a row per group and whatever details you need
  • UsersGroups with a unique combination of UserId, GroupId that only keeps track of relationships

This will let you have as many combinations as you like.

  • Thank you for answering. How would the UsersGroups table look like though? If we have user 001 who belongs to groups 001 002 003 would that be 3 rows in the UsersGroups table? Should each row in the UsersGroups table have a unique ID? Why?
    – bernk
    Jul 1, 2012 at 11:04
  • I prefer single names for tables. UserGroup table will be: (UserID, GroupID, any other information related to the association). In this case, UserID + GroupID represent the Primary Key. You could also use a separate ID column but you must have the User+GroupID as a unique constraint in all cases.
    – NoChance
    Jul 1, 2012 at 12:35
  • @bernk Yes, one row per relation. And you don't really need another key here, since you will want a unique constraint or PK on the combination of UserId, GroupId to prevent duplicates.
    – JNK
    Jul 1, 2012 at 15:14
  • 1
    I was referring to making that combination either a PK or putting a UNIQUE CONSTRAINT on those fields to enforce uniqueness within the database.
    – JNK
    Jul 1, 2012 at 19:13
  • 1
    @bernk: You are right that this is getting away from the original question. It would be better to make a basic design that you think that fits your purpose and post another question (on how it can improved or if there are flaws) Jul 1, 2012 at 20:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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