Member networking/friends list - Table design

Question

Post reply

Member networking/friends list - Table design

Senthil T

Hall of Fame

Points: 3367
More actions
April 2, 2007 at 7:12 pm

#116365

Hi,
I am working on this new website. Each registered user has his own profile page, and he can add other users as his friends, if they approve his request. (The friends list appears as a list on the users profile page) And if user1 is a friend of user2, then user2 is a friend of user1. That is, they appear on each other's friends list. This is the table I have come up with.
TableFriends - userid, requestfriendid, requestdate, status
requestfriendid is the userid of the friend who initiated the requested. Status field denotes whether the request was approved or denied. Any suggestions or feedback on the table design?
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

Representing an Organizational Structure

I wasn't born stupid - I had to study.

Senthil T Hall of Fame Points: 3367 More actions · Answer 2

I believe it talks about heirarchical tree structure. This friends table would be a non-directed graph. I think this table design is OK, I have all the select, insert, and delete queries, not complex but not sure how efficient they are. I just want to get others' opinion. If there is a better/easier way to design the table I would like to know.

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Senthil, think about this.

If we are friends, how many rows do you create?

One?

Which one?

Senthil|Sergiy

or

Sergiy|Senthil?

Then, if you need to find friends of Senthil, which column are you gonna scan?

Or both? It makes 2 queries instead of 1.

If you create 2 records you get twice as big as it supposed to be.

Then if Farrell became friend of Sergiy, is he a friend of Senthil?

If yes, which of them will be first in the record "Farrell|Senthil"?

Have you pictured records for 3 friends: Sergiy, Senthil, Farrell?

What about query to retrieve list of friends for each of them?

What if Farrell is really friendly person and he has 20 friends?

Do this homework and see if this design is good enough for you.

_____________
Code for TallyGenerator

Steve Jones - SSC Editor SSC Guru Points: 736568 More actions · Answer 4

If you don't have two records in there, rather it would be a union of two queries.

If you ahve

TableFriends

OriginalFriend Friend Approved

Steve Senthil Yes

Bob Senthil No

Andy Senthil Yes

Senthil Bill Yes

You'd need to query

select * from TableFriends where OriginalFriend = 'Senthil'

and approved = 1

union

select * From TableFriends where Friend = 'Senthil' and approved = 1

You can cover this query, and that might be the best way. Or are you looking to have friends of friends appear? In that case, then you quickly get into a recursive query. Or perhaps listing all friends isn't something you'd do often. If that's the case this makes sense.

If you want to add multiple records, then this problem occurs whenever someone is approved. You'd need to add all the related records, which could be a problem as well.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

Some useful readings

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72097

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73079

N 56°04'39.16"
E 12°55'05.25"

Senthil T Hall of Fame Points: 3367 More actions · Answer 6

"Then if Farrell became friend of Sergiy, is he a friend of Senthil?" No. There is no friends of friends or recursive relationship.

I don't want to add multiple records, if it doesn't improve anything. Union of two queries, thats what I have currently. I think I will stick with it.

Thanks.