0

I am not a database designer by any means. I am a desktop programmer, but I am currently looking at implementing a website idea which will need a (possibly very large) back-end database.

In my site I will have users who can create and join "groups". The number of users could be rather larger, as could the number of groups, and users could obviously be in more than one group if they so desire.

I am struggling with how to properly define this on the database end.

I could create a "groups" table with all the group information, and then another table that associates each user with groups (so each row in that table would have a user id and then a group id, and the user id could appear in multiple rows since a user could be in multiple groups). Such a table would grow rather large with a large number of users, especially if users were involved in multiple groups.

A friend of mine suggested having a separate table for each group instead. So every time a user creates a group, we create a table in the database which then gets populated with the user ids of each user that is in that group. I wasn't so sure about this, because it seems like it would be a lot of overhead to create a new table for each group.

Also, coming from a desktop programmer's perspective, I really just want to create a class called "Group" and give it an array of user ids...but obviously I can't do that in the relational database world.

So, what can I do? What's the best way to design this? What will run most efficiently on a server? Help enlighten a programmer who is wanting to learn a little bit about database design. :-)

Thanks!

Improve this question
2
  • 1
    You may find this resource very helpful. (gbn always recommends it.)
    – Nick Chammas
    Sep 15, 2011 at 23:39
  • 2
    You should tell your friend that you aren't ever going to ask them for database design advice ever again. Give them beer when they help you move, but don't let them into your database.
    – Joel Brown
    Sep 15, 2011 at 23:40

1 Answer 1

Reset to default
6

Your first option is most likely the best. A table called users, a table called groups and a junction table called user_groups. User_groups allows for the many to many relationship between groups and users. As for size, what are you thinking in terms of "large"? 20,000 records? A million? It might be possible database types and programmers think differently in terms of "large", because I can't foresee this table having more than a few hundred thousand rows if the website gets popular and there are a lot of groups and that doesn't seem large. As long as the tables are indexed properly and the queries are written efficiently it should not be an issue...you would only be querying out of the table for specific users or specific groups anyway. Whichever RDBMS engine you use is designed to operate on tables structured this way. There's no good design reason to break the groups table into multiple tables - it means having to maintain new tables for each new group exactly as you said, plus you run into complicated queries where you want to bring back data from more than one group at once.

Improve this answer
2
  • You should also have columns in the group and user tables for date created, date last modified, created by, last modified by and whether the group or user is disabled
    – kevinskio
    Sep 15, 2011 at 23:14
  • @Kevinsky, for certain. I'm assuming that any queries against these tables would be targeting specific things (give me all the groups this user belongs to, give me all the users in this group, give me all the users that belong to the groups that this user belongs to)/
    – Wil
    Sep 15, 2011 at 23:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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