2

I am working on a program where I would like to like create a Group with Users which is then always there and saved in a MySQL Database. I just don't have any clue how to do this. I mean I could create a Table like:

GROUPID INT PRIMARY KEY
GROUPNAME TEXT
USER1ID INT
USER2ID INT
USER3ID INT

Then I would have to create A table which can take like lets say in this case 3 Users but isn't that a not performant and no possibility to dynamically make the group bigger. If I would like to make a friends list out of the Group then I also can't say that the User can have only 3 Friends. How can I solve this problem?

ekad
  • 14,436
  • 26
  • 44
  • 46
alexj
  • 139
  • 1
  • 4
  • 15

1 Answers1

4

You need 2 tables:

GROUPS - GroupId, GroupName
USERS - UserId, UserName, Password, GroupId

The GroupId in USERS links user entries to GROUPS. You can (should) also impose the link using a foreign key.

If a user can belong to more than one group, you will need a many-to-many relationship. This is achieved with a 3rd table that looks like this:

GROUPS - GroupId, GroupName 
USERS - UserId, UserName, Password
USERS_GROUPS - UserId, GroupId
  • I already have a Users table but I am asking myself how I can dynamicly let a Group have as much Users as ist wants to have. Because in my example up there the USER1ID is a FOREIGN KEY but I can only have 3 Users in the Example. WHat I want to do is let the Group have as many members as wanted. – alexj Dec 17 '12 at 13:04
  • well... the USERS table is tied to the GROUPS table, not the other way arround. you should read a bit about 1-to-many relationships: http://www.databaseprimer.com/relationship_1tox.html –  Dec 17 '12 at 13:06
  • Yes I unterstand now Thank you. What a simple solution -.- – alexj Dec 17 '12 at 13:08
  • thinking about it, since a user can theoretically belong to more than one group, you should also look into many-to-many relationships (i'll also update my answer): http://www.tomjewett.com/dbdesign/dbdesign.php?page=manymany.php –  Dec 17 '12 at 13:15