How should I store a list in a database

Question

How should I save a list of int(s) in a web app ?

I am designing a facebook-like app and for every user I need to save the ids of his friends, so a csv in the DB I don't think is indicated taking into account that a user cam even have 1000 friends and the database field that should contain this list is fixed, the csv may overflow that field.

Should I store these lists in local server files or is the database still the best option?

Answer 1

Many-to-many relationships are typically handled using a separate table.

user  |  friend
---------------
1     |  2
1     |  3
2     |  3
etc

You then use a JOIN to find out who is friends with a given user

Answer 2

What you are looking for is a join table, joining Users to itself. An entry in this table would represent a friendship between one user and another.

Users table

UserID | UserName | FirstName | LastName ...

Friends table

ID | UserID | FriendID

Both UserID and FriendID would be foreign keys to the Users table. You'd probably want to have a uniqueness constraint on the pair (UserID,FriendID) and a non-unique index on UserID. Note that UserID is the PK for the Users table and ID is the PK for the Friends table. Using a separate PK for the Friends table will make it easier to refer to a particular user/user pair in your interfaces.

Answer 3

You shouldn't store a list in a single field of the database - you will not be able to easily query it. Multi-valued columns are almost always the wrong choice.

In terms of database design, you should have a many-to-many table to connect users to friends (you may want to also store the corresponding opposite relationship in your table, for easy traversal of the bi-directional relationship).