1

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?

Improve this question

1 Answer 1

Reset to default
2

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.

Improve this answer
2
  • So to clarify, data handling-wise, it's just a basic many-to-many relationship and functionally-wise, as far as how it functions the way facebook allows users to add new interests and select from a list of already created interests, that part is all in the coding... correct? EDIT: Just read your edit saying the same thing... cool ^.^ I don't plan on out Facebooking Facebook, but I would like to create a function similar to it, although, I'll probably go with an easier method like a drop down list of existing groups and a special field to add a new group.
    – user1263500
    Mar 14, 2012 at 7:51
  • The drop-down list will be fine for small numbers of groups, but it won't scale up very well - once the list gets beyond what people can comfortably scan before their attention drifts (10-20 items? I'm not sure, no UX expert), you'll need to provide a search/filter functionality.
    – Simon Righarts
    Mar 14, 2012 at 8:02

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.