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