Click here to Skip to main content
12,943,742 members (62,214 online)
Rate this:
Please Sign up or sign in to vote.
See more: , +
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(Table Name)
user_id- primary key
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
__TR__ 1-Feb-13 8:10am
SELECT * FROM Friend WHERE user_id = [User Id value] OR user_id_friend = [User Id value]

1 solution

Rate this: bad
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.


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

    Print Answers RSS
Top Experts
Last 24hrsThis month
OriginalGriff 4,643
CHill60 2,970
Maciej Los 2,348
Jochen Arndt 1,900
ppolymorphe 1,765

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