Php explode or unique Mysql table for user's friends?

Question

Hello,

I will create frienship system for my project. I couldn't decide whether I should store friends in a column at user's row or create a friends table and store there with user IDs? To clearify which usage is better:

Should users table be like this?

+-----------+-----------+---------------+
+    id     + username  +    friends    +
+-----------+-----------+---------------+
+    11     + user_x    + 12;23;15;3;7  +
+-----------+-----------+---------------+

with

<?php explode(';',$friends); ?>

or; Sould I create a friends table like this?

+-----------+-----------+---------------+
+    id     +   user    +     friend    +
+-----------+-----------+---------------+
+    1      +    11     +       12      +
+-----------+-----------+---------------+
+    2      +    11     +       23      +
+-----------+-----------+---------------+
+    3      +    11     +       15      +
+-----------+-----------+---------------+
+    4      +    11     +       3       +
+-----------+-----------+---------------+
+    5      +    11     +       7       +
+-----------+-----------+---------------+

with

SELECT friend FROM friends WHERE user = 11

Which one is better? Thank you

Answer 1

The latter. It allows you much more flexibility.

Imagine cases such as deleting a friend, or countinhg how many friends someone has, or looking at two people and seeing what friends they ahve in common. All are trivial with the second system, not so in the first.

Answer 2

The second is lot more flexible, for example, it makes it easy for you to query for 'people who have friended you'. Unfriending simply involves deleting a single row rather than manipulating a string. You can easy generate stats on how many friends people have, etc...

You can still achieve the effect of the first option if that's important for you. Simply use GROUP_CONCAT to get all the friend ids in a list, e.g.

 SELECT id,foo,bar,GROUP_CONCAT(friend_id) as friends 
 FROM user 
 LEFT JOIN friends ON(user.id=friends.user)
 GROUP BY user.id;

Answer 3

It depends on your usage. If you're only going to be displaying the list of friends and never search through it (eg is 14 a friend of 11?), then perhaps the first may be enough. Deleting or inserting to it will require first getting the friends list, changing it, and then updating the record with the entire new list.

However, for anything more than that simple usage the second one is much more flexible, as long as both user and friend are indexed properly.