CODEWITHSUNDEEP
mysqldatabase-designsocial-networking
Nicklas Pouey-Winger
Nicklas Pouey-Winger

Reputation: 3023

What is a good way to design a table with relationship between users?

I'm currently working on a social networking site. (Yeah, I know, there's a whole bunch of them. I'm not trying to make Facebook all over)

I was wondering if anyone could tell me if my way of thinking is way off, or if it is the way it is actually done.

I want a user to be able to have friends. And, for that, I'm thinking that I should have one usertable like so: USER

This should probably be a 1:N relationship, so I'm thinking that a table "contacts" should hold a list of users and their friends like so: CONTACTS

Would it be an effective solution to sort this table on uId, so that a query result would look something similar to this:
uID | friendId
1 | 2
1 | 6
1 | 97
75 | 1
75 | 34

etc

Or are there any different solutions to this?

Upvotes: 0

Views: 291

Answers (2)

DJSunny
DJSunny

Reputation: 1960

If you are simply looking to select a specific users set of friends, the query will be straightforward and you won't have to worry about performance.

For example: If you are looking to return the id's of UID 8's friends, you can just do something like:

Select FriendId FROM TABLE where UID=8;

In your case, since the UID column is not unique, make sure to have an Index on this column to allow quick lookup (optimize performance).

You might also want to think about what other data you will need about the users friends. For example its probably not useful to just grab the FriendIds, you probable want names etc. So your query will likely look more like:

Select FriendId, Users.name FROM Friends JOIN Users ON Users.uid=Friends.FriendId WHERE Friends.UID=8;

Again, having the proper columns indexed is key for optimized lookups, especially once your table size gets big.

Also, since the act of adding friends is likely very uncommon in comparison to the number of lookup queries you do, be sure to choose a database engine that provides the fastest lookup speed. In this case MyISam is probably your best bet. MyISam uses table level locking for inserts (i.e. slower inserts) but the lookups are quick.

Good luck!

Upvotes: 1

JNDPNT
JNDPNT

Reputation: 7465

I think the best way is without doubt creating a table like you proposed. This will allow you to better manage the friends, do query's for friends on this table, ... this would be the best solution.

Upvotes: 0

Related Questions