Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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. –  ypercube Jul 1 '12 at 20:32
add comment

1 Answer

up vote 4 down vote accepted

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.

share|improve this answer
    
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 '12 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. –  Emmad Kareem Jul 1 '12 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 '12 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 '12 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) –  ypercube Jul 1 '12 at 20:29
show 5 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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