How do you structure a database that allows for groups to be formed/added by users and also be a part of like facebook?

Question

I'm new to databases and web development so a laymen explanation is best for the time being. I'm only in the data modeling phase so no need for specific code. I'm just trying to wrap my head around how the data would be managed and I'll get around to figuring out code when the time comes. I already understand the concept of OneToMany and ManyToMany, so no need to explain that to me.

Basically, what I'd like to do is for users to be able to create a group and name it whatever, and also have a list of already existing groups for a user to add themselves to, similar to the way Facebook's "interests" works. Users can type in whatever interest they want and if it already exists, they get added to a group of people who have that interest.

Which tables need to be created? Obviously I'll need a UserTable, InterestTable... anything else? How would the tables relate to one another? What keys would each table need?

Answer 1

You need three tables - User, Interest and an associative table for the many-to-many relation between them (a User can have many Interests and an Interest will have many Users), call it UserInterestLink or something. User and Interest will have autoincrement int fields as a surrogate primary key, UserInterestLink will have FKs to both User and Interest, like so:

|=================================================|
| TABLE: User                                     |
|=================================================|
| Name       | Type                               |
|=================================================|
| UserID     | int, autoincrement, PK             |
| << Other User fields here >>                    |
|=================================================|


|=================================================|
| TABLE: Interest                                 |
|=================================================|
| Name       | Type                               |
|=================================================|
| InterestID | int, autoincrement, PK             |
| << Other Interest fields here >>                |
|=================================================|


|=================================================|
| TABLE: UserInterestLink                         |
|=================================================|
| Name       | Type                               |
|=================================================|
| InterestID | int, FK to Interest.InterestID     |
| UserID     | int, FK to User.UserID             |
|=================================================|

I didn't mention the PK on UserInterestLink, for efficiency it would be a compound PK on (UserInterestLink.InterestID, UserInterestLink.UserID), but there's also an argument to be made for a seperate surrogate primary key (which I personally think is a waste of space, unless you need to allow for multiple UserInterestLinks between one User and one Interest). For a purely associative table in a many-to-many relationship, though, the compound primary key should work fine.

Edit: The complicated part of this isn't in the relational design, it'll be in the application code, because you'll need best-match/partial matching in order to show users the groups that most closely match their interests (rather than having accidental splinter groups all over the place). And if you want to out-Facebook Facebook, you'll need a smoother and better user experience than they offer.