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 0:48am
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 Sergey Alexandrovich Kryukov 853
1 OriginalGriff 410
2 CPallini 275
3 George Jonsson 226
4 Richard Deeming 145
0 OriginalGriff 5,450
1 CPallini 4,500
2 Sergey Alexandrovich Kryukov 4,482
3 George Jonsson 3,057
4 Gihan Liyanage 2,445


Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 3 Feb 2013
Copyright © CodeProject, 1999-2014
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