setting up a friend list in mysql

Question

I want to make a friends list in my online game. I am not sure how to set it up or where to start. The add friends and accept friends part I can handle, but I don't know how to set it up in mysql and php. A list of users (friends) connected to each user or something?

Answer 1

If your friendship relationship is symmetrical, you can either store each pair in a separate record:

friend1 friend2
A       B
B       A
A       C
B       D
C       B
D       B

and query all B's friends like that:

SELECT  friend2
FROM    friends
WHERE   friend1 = 'B'

or store the user with the least id in the first field and that with the greatest id in the second one:

friend1  friend2
A        B
A        C
B        D

and query B's friends like that:

SELECT  friend1
FROM    friends
WHERE   friend2 = 'B'
UNION ALL
SELECT  friend2
FROM    friends
WHERE   friend1 = 'B'

The first option is a little bit more efficient in MySQL, and this is the only option if your friendship relationship is not symmetrical (like on LiveJournal)

See this article:

• Selecting friends

Answer 2

A friendship is essentially a mutual relationship between two people. In database terms it's a many-to-many relationship between two users.

So what you need is a linking table that holds references to two users by ID.

Example

Users table

ID    Username
1     Bob
2     Jim
3     Alice

Friends table

user1    user2
1        2
2        3

This would make Bob friends Jim and Jim friends with Alice.

Answer 3

Check out the answers in this other post here on stackoverflow...some simple but great explanations to accomplishing what you need.

Facebook database design?

Answer 4

An alternative would be to set column A as follow: $checkfriend = (($friend1 $friend2) || ($friend2 $friend1))

Column B as follow: (friendship accepted or friendship rejected) based on user's choice

Basically have both user1 and user2 in the same column.