Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
Hey Sorry the title is not very clear.I want to design a table to store friendship relationship in my web project
I have a following friends Table

Friend(Table Name)
friend_id - primary key
user_id
user_id_friend
status

User(Table Name)
user_id- primary key
userName
-----------------------------------------------------------------------
status e.g.(0 denotes rejected whereas 1 denotes accepted or 2 denotes unprocessed
It should satisfy at least the following 4 conditions:

The way the table is populated is - when I send a friend request to John - my userID appears in user_id and Johns userID appears in user_id_friend.

Now another scenario is say Mike sends me a friend request - in this case mike's userID will appear in user_id and my userID will appear in user_id_friend

So to find all my friends - I need to run a query to find where my userID appears in both user_id column as well as user_id_friend column

What I am trying to do now is - when I search for user say John - I want all users Johns listed on my site to show up along with the status of whether they are my friend or not and if they are not - then show a Add Friend button.

If any of you are experienced with this , any suggestion is welcomed

my current design (I think bad right now)

please suggest me a sql to retrieve the data.

thanx in advance.
Posted 1-Feb-13 1:48am
ishandeb1.1K
Comments
__TR__ at 1-Feb-13 8:10am
   
Try
SELECT * FROM Friend WHERE user_id = [User Id value] OR user_id_friend = [User Id value]

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

I'd say for the specific needs you stated,
you can use only one table and have a boolean field of IsFriend.

Even if you add more functionality and thus more fields to the Friend entity, you can still have the USER table hold friends and another table for FriendSpec i.e. other attributes of Friend that are additional to a regular User.

Cheers,
Edo
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 280
1 Sebastiaan Meijerink 145
2 Sergey Alexandrovich Kryukov 108
3 Abhinav S 94
4 BillWoodruff 85
0 Sergey Alexandrovich Kryukov 6,558
1 OriginalGriff 6,288
2 Peter Leow 2,534
3 Abhinav S 2,358
4 Maciej Los 2,352


Advertise | Privacy | Mobile
Web02 | 2.8.150414.1 | Last Updated 3 Feb 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100