How do I structure a database to handle users in groups?

Question

I've been working on a project that involves having a number of users. Each user can be in any number of predefined groups, and I'd like a user to only be able to see another user if they share at least one group.

Right now I have a table of users, and a table that has one column for each group, and one row for each user, and a boolean entry specifying whether the user is in the group. The problem is that this makes the query to determine whether a user can see another user is complicated, and if I want to add another group I have to change this query to account for the additional column.

Is there a better way to structure this in a relational database? (I'm currently using PostgreSQL , which I don't think matters)

Answer 1

Couldn't you use three tables, like this:



USERS

user_id - primary key

password

etc



GROUPS

g_id - primary key

created_by

other meta data



GROUPMEMBERS

user_id - primary key

g_id - primary key (as in use user_id and g_id as a joing primary key)



So you store the list of groups in one table (Groups) then relate users with groups in another table (Groupmembers).

Answer 2

This doesn't sound like a database issue, but an application issue. The application will produce the SQL that will select the users based on criteria. Hence if you tell the application, "select all users with group XX", the SQL server doesn't care WHAT the data is, just HOW to store and retrieve it.



If you're interested in an advanced group/roles authentication package, look at PHPGacl, or simpler, Pear's AUTH package. Even if you're using a different language it should help you see how to manage users and groups.



Hope this helps.

Answer 3

Your structure may be just fine. I'm not sure why you would need the boolean column. What if your group table just had the groupid and the userid? They either have an entry for a group or they don't. Your "friend" query then just has to determine the groups friend-A is in and compare them to the groups of friend-B.

Answer 4

A single group can have multiple users and a user can be in multiple groups. This relationship is termed as many-to-many. You must have list of users in User table and Groups in Group table. You should also create a "Assoc" table to associate a user and group. You can see an example here http://gradcenter.marlboro.edu/~iank/wad/2/#relational (hit ctrl+end... the last part has the example)

Answer 5

One table has userid,name,groupid, next table has groupid, group name, next table has userid, groupid

select u.name from users u

join groups g on u.groupid = u.groupid

join groupusers gu on u.userid = gu.userid and u.userid = gu.userid