Best way to store arrays of variable length in MySQL

Question

I want to make an app that allows users to add other users to a personal friendslist. In my database there is a table called 'users'. Every user has a unique id and a unique username, now every user needs to be able to have a list of friends.

I think the best option to save these friendslists is to create a seperate table with two colums, for every user. One column for the friends' id's and one for their usernames.

I can search and retrieve the friends username and id at the same time. On the downside I will need to create a hugh number of tables (hundreds, thousands, perhaps millions), one for each user.

Will this make selecting a table from the database slow? Will this unnecessarily cost a hugh amount of space on the server? Is there a better way to save a friendslist for every user?

Answer 1

You should not do that.

Instead do something like

UserTable
* Id
* UserName

FriendsTable
* UserId
* FriendId

You may need to read a little about relation databases.

This way a user can be friend to a lot of people. Consider for this example

UserTable
1, Joey
2, Rachel
3, Chandler
4, Ross
5, Phoebe
6, Monica

FriendTable
1, 2
1, 3
1, 4
1, 5
1, 6
2, 3
2, 4
2, 5
2, 6
3, 4
3, 5
3, 6
4, 5
4, 6
5, 6

Here the people from Friends is all friends to eachother

Answer 2

I don't think you need to go down that route. If you have a table of users (user_id, user_name) for example and another table of friendships (friendship_id, user_id1, user_id2) then you will be able to store all friendships in one table. The unique id being friendship_id.