database design - MySQL friends table

Question

I have a MySQL DB in which I store data about each user.

I would like to add a list of friends for each user. Should I create a table of friends for each user in the DB or is there a better way?

Answer 1

Assuming all your friends are also in the user table you will need a friends table which defines a simple one-to-many relationship - linking the users table back to itself. So

User Table
UserID int identity not null
[other attribute fields]

Friends Table
UserIDLink1 int
UserIDLink2 int 
[other attribute field]

Where both UserIDLink1 and UserIDLink2 are foreign keys on the Users table.

So for instance if I have three users

1 Joe
2 Bill
3 Jane

and Joe and Jane are friends then the Friends table would contain a single row

1 3

The above implicitly assumes that if A is a friend of B then B is a friend of A - if this isn't the case you'd probably want to rename UserIDLink1 and UserIDLink2 to UserID and FriendID or similar - in which case you'd have up to double the records too.

Also for the bi-directional configuration (A is a friend of B if B is a friend of A) you should set up indexes on the Friends table for (UserIDLink1,UserIDLink2) and (UserIDLink2,UserIDLink1) to ensure access is always efficient if we were searching either for friends of joe or friends of jane (if you didn't set up the second index then the first query would be an efficient index lookup but the second would require a full table scan).

If your links were not bidirectional this wouldn't be necessary to find out who A's friends are, but you would still probably most require it as you'll likely also need to find out who B is a friend of.