Store and retrieve friends contact list using mysql

Question

Hi

I want to create a database that can store the friends contact list as like social networking what is the best way to design the database structure and easy to retrieve the contacts of friends using mysql.

i need solution for this, HELP ME

regards ~Deepu~

Answer 1

The best way to model heriarchical data depends on what operations you need to support. I would suggest that you read Bill Karwin's slides Models for heirarchical data for a comparison. See in particular slide 48 where there is a summary of the strengths and weaknesses of each approach.

However, I wouldn't regard friendship as a heirarchical structure. There will normally be loops: A is friends with B, B is friends with C, and C is friends with A. Instead you can create a contact table with two columns: user_id and friend_id which are foreign keys into the "users" table:

contact_list
------------------
user_id  friend_id
------------------
1        2
2        3
3        1

To retrieve the contact list for a specific user id run this query:

SELECT friend_id
FROM contact_list
WHERE user_id = 1

Here I'm assuming that A being on B's contact list does not imply that B is also on A's contact list.