Should I store as friends as "strings" or "integers" in php and mysql?

Question

Hi, I am in dilemma, do not know which one is better. (I am using php and mysql btw)

Lets say I am developing a social community website. A user can add as many friends as he wants. In my database, I have a users table which store user's details include user's friends. Should I store the user's friends in:

Strings => john;bryan;sam;paul;...;

or

Integers => 1;21;50;1779;30;...;

String means store their username. Integer means store their user id.

Which one is better?

If string, it would be a very, very large string(imagine the users have 400 friends, and some usernames are extreme long)

If id, how to list friends on user profile page? Getting list of IDs in one long string (separated by semicolon), use explode function to make it into array. Then using loops to display?? Isn't troublesome? Is there any other way? If yes, please show some sample code..

OR normally, how would u guys to store the "friends" list in the user table?

Answer 1

Normally you would create a third table "users_friends", with two columns, userId and friendId, both making up the primary key. A quick Google came up with this: http://www.tekstenuitleg.net/en/articles/database_design_tutorial/8 , but you might want to look up other articles on "many-to-many relationships".

Answer 2

Don't store them as a semi-colon delimited anything. Store them as user IDs in a lookup table. Suppose the user is #2. You might have:

SourceUserID | FriendUserID
2              1
2              21
2              50
2              1779
2              30
1              // One of John's friends

Then to display the list of friends, you query this table restricting it by SourceUserID = 2 and join against the user table to get the names. For example:

SELECT Name FROM SITE_USERS
INNER JOIN USER_FRIENDS ON SITE_USERS.ID = USER_FRIENDS.FriendUserID
WHERE SourceUser = ?

(Or whatever your parameterised query format is; populate the parameter with the ID of the user whose page you're displaying.)

Answer 3

This requires whats called a many to many relationship (ie A friend can belong to many users and users can have many friends) so you need to create a third table to store the link, the table will have a uniqueId, a Friend ID and a User ID,

To get a users friends you need to write a joined SQL statement, ie

SELECT Friends.FriendName FROM UserFriends 
   INNER JOIN Friends ON Friends.FriendId = UserFriends.FriendId 
   WHERE UserID = @intUserId

(@intUserId of the user you want to list the friends for)