How to store Friends and Friends of friends In my Database

[kiel19]

I am developing a database whre people can register and connect with friends. I am stack at how to store friends. Should I create a Table Friends with fields Friend_ID, Reg_ID, Reg_ID_2. Reg_ID and Reg_ID_2 are values from the Registration table which is the primary key.

Thank you for your help.

[jbisono]

I guess you only need two tables to accomplish this a "PERSON" table which hold all general information. and a "friend" table contains two fields both pimary keys, "ID_PERSON" and "ID_PERSON_FRIEND" in this case to get the person's friend should be very straightforward

SELECT *
FROM         PERSON INNER JOIN
                      FRIEND ON FRIEND.ID_PERSON = PERSON.USER_ID INNER JOIN
                      PERSON AS PERSON_1 ON FRIEND.ID_PERSON_FRIEND = PERSON_1.USER_ID
WHERE     (PERSON.USER_ID = 1)

the above statement will give all friends that belong to the user_id = 1 in the same way you can get the friends of the user_id = 1's friends. let me know if you have any trouble doing that.

regards

[drjohn]

re Friend_ID, Reg_ID, Reg_ID_2 as fields

Friend_ID is not needed, the joint key of Reg_ID, Reg_ID_2 would be unique, AND you'd have to declare it unique if you did it your way, otherwise someone could be listed two or three times as friend of the same person. But using the joint primary key immediately gets round this problem.

Many people have a bad habit of automatically adding a field called ID when there is a perfect natural key available. Then they have more work to do to avoid problems such as the one I mentioned. And they also often need extra joins to get at the data they need.

[kiel19]

Thank you for the support.