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
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;