I an developing a social networking site. Please tell me what will be table structure and query to store the friends and retrieving all friends related to the logged in members.
The most typical way would be to have a users table, and then a user_relationship table that stores the relationships between the users. Something like: USER_TABLE user_id user_name other_user_info USER_RELATIONSHIPS relationship_id user_id friend_id relationship_type Both the user_id and friend_id columns relate to the user_id of the Users table. This way you can add as many relationships to a single user as you want. You would want a unique composite index on user_id, friend_id to prevent a user from adding the same person multiple times. A foreign key to the Users table is a good idea as well. This is a very basic, but normalized structure. This should be the easiest and least maintenance method to modify and scale in the future.
Thanks jestep Consider Scenario: A and B are two users A added B in his friendlist. Now both are friend of each other. Please write a query give friend result. Like if A login to his acount his friend will be B And vice-versa.
Hi, SELECT `user_table`.*, `user_relationships`.* FROM `user_table` LEFT JOIN `user_relationships` ON `user_table`.`id`=`user_relationships`.`user_id` WHERE `user_relationships`.`friend_id` = '1' Code (markup): 1 - is id user A I am not check it because I am not sure.
SELECT `user_table`. * , `user_relationships`. * FROM `user_table` LEFT JOIN `user_relationships` ON `user_table`.`id` = `user_relationships`.`user_id` WHERE `user_relationships`.`user_id` = '1' Output --------- id user_name relationship_id user_id friend_id relationship_type 1 ------ om ----- 1 ---------- 1 ----- 2 1 ------ om ----- 2 ---------- 1 ----- 3 The aboove result shows that user_id 1 have 2 friends 2 and 3 But if i will pass user_id=2 or 3 in the above query it returns 0 rows where as 2 and 3 should output user_id 1 as its friend