What is the best practice for storing friend relationships with MySQL?
I currently have:
FRIENDSHIP ID | FRIEND1_ID | FRIEND2_ID | STATUS | DATE_ESTABLISHED
1 1 2 accepted now()
2 1 3 incoming now()
3 1 4 outgoing now()
But would it easier to manipulate in PHP by storing a 2-way relationship for each friendship such as:
FRIENDSHIP ID | FRIEND1_ID | FRIEND2_ID | STATUS | DATE_ESTABLISHED
1 1 3 incoming now()
2 3 1 outgoing now()
Which would turn into:
FRIENDSHIP ID | FRIEND1_ID | FRIEND2_ID | STATUS | DATE_ESTABLISHED
1 1 3 accepted now()
2 3 1 accepted now()
If I keep it as is, what queries would I need for:
inserting a new relationship (user 1 adds user 2)
listing only incoming friend requests for user 1
Store your users:
ID | USERNAME
Store the friendships:
USER_ID1 | USER_ID2 | STATUS | DATE_ESTABLISHED
bonus point: this schema allows for non mutual relationships :-)
Also, I wouldn't update the "status" field when the status changes. What I would do is to create a new row with the new status. This way you have the history of the statuses and you can also efficiently search for the "current friendship" (sorting by timestamp)