Find solution
How would I structure database the best?

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)

  • Answer:

    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).

gstoll2003 at Yahoo! Answers Visit the source

Was this solution helpful to you?

Other answers

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).

LackLust...

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.

Steve

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

SanDiegoGuru92107

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)

venkks

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.

Mutant

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.

Mutant

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.

Steve

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)

venkks

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

SanDiegoGuru92107

Related Q & A:

Just Added Q & A:

Find solution

For every problem there is a solution! Proved by Solucija.

  • Got an issue and looking for advice?

  • Ask Solucija to search every corner of the Web for help.

  • Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.