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!
Best Answer
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.