(No Ratings Yet)
Loading ...How do I structure a database to handle users in groups?
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)
Posted in 
Tags: 5 Responses to “How do I structure a database to handle users in groups?”
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.
Report this comment
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).
Report this comment
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)
http://gradcenter.marlboro.edu/~iank/wad/2/#relational
Report this comment
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.
http://phpgacl.sourceforge.net/
http://pear.php.net/package/Auth
Report this comment
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
http://phpgacl.sourceforge.net/
http://pear.php.net/package/Auth
Report this comment