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.
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
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.
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.
Hi, I am a newbie in developing a website. i have tried to connect using mysqli conncetion
Here is my simple code.
// db.php code
//$con = mysqli_connect("localhost","root","","bakery"); ...