2

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?

ganjan
  • 7,356
  • 24
  • 82
  • 133

4 Answers4

11

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:

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • Do you need to assign a primary key still on the relationship table? – MikeS Jan 12 '12 at 19:04
  • @Mike: of course you do, to get rid of duplicates and to speed up searches. The relationship table can also have attributes on its own (like a date of the friendship established etc). – Quassnoi Jan 12 '12 at 21:30
  • If I understand correctly, you're talking about composite primary keys, right? Both primary keys and both foreign keys referencing back to the "users" table. – MikeS Jan 13 '12 at 01:17
  • @MikeSanchez: `CREATE TABLE friendship(a INT NOT NULL REFERENCES users, b INT NOT NULL REFERENCES users, PRIMARY KEY (a, b))` – Quassnoi Jan 13 '12 at 05:13
5

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.

  • Hi @robinjam! Just wanted to check with you regarding this. If my friend list is symmetrical, should it go like this: If 1 is friends with 2 then 2 is friends with one. Creating 2 row entries in the table for their relationship. Thanks! – MikeS Jan 06 '12 at 10:47
  • Mike Sanchez: The Friends table is modelling mutual friendships, so you'll want 1 row per friendship, not 1 row per person. –  Jan 07 '12 at 05:29
  • I was going to try and use this kind of set up in a php+mysql environment. Correct me if I'm wrong but with your kind of set up, I would have to use UNION. As opposed to creating 2 entries in the table I can just use a single WHERE user1="1" statement. – MikeS Jan 07 '12 at 21:10
  • Mike Sanchez: Depends what you're trying to do. Could you open a new question rather than posting comments on this one? –  Jan 09 '12 at 01:22
1

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

Facebook database design?

Community
  • 1
  • 1
swisscheese
  • 1,765
  • 4
  • 23
  • 25
-1

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.

Odinga
  • 1