Many database rows vs one comma separated values row

I'm creating a table for allowing website users to become friends. I'm trying to determine which is the best table design to store and return a user's friends. The goal is to have fast queries and not use up a lot of db space.

I have two options:

Have individual rows for each friendship.

+----+-------------+-------------------+
| ID | User_ID     | Friend_ID         |
+----+-------------+-------------------+
| 1  | 102         | 213               |
| 2  | 64          | 23                |
| 3  | 4           | 344               |
| 4  | 102         | 2                 |
| 5  | 102         | 90                |
| 6  | 64          | 88                |
+----+-------------+-------------------+

Or store all friends in one row as CSV

    +----+-------------+-------------------+
    | ID | User_ID     | Friend_ID         |
    +----+-------------+-------------------+
    | 1  | 102         | 213,44,34,67,8   开发者_如何转开发 |
    | 2  | 64          | 23,33,45,105      |
    +----+-------------+-------------------+

When retrieving friends I can create an array using explode() however deleting a user would be trickier.

Edit: For second method I would separate each id in array in php for functions such as counting and others.

Which method do you think is better?

---

First method is definitely better. It's what makes relational databases great :)

It will allow you to search for and group by much more specific criteria than the 2nd method.

Say you wanted to write a query so users could see who had them as a friend. The 2nd method would require you to use IN() and would be much slower than simply using JOINS.

---

The first method is better in just about every way. Not only will you utilize your DBs indexes to find records faster, it will make modification far far easier.

---

Breaking from 1st normal form is usually not desirable because

1. Easy to Orpahned ids
2. Easy to insert invalid data types
3. Updates can require full table scans
4. Increases concurrency issues
5. No way to create the key (user_id, friend_id)

---

Use the power of the relational database. Definitely go with the first approach. MySQL is faster than you think, and it regularly deals with VERY large datasets.