I am developing a table for permitting website customers being buddies. I am attempting to determine the best idea table design to keep and return a user's buddies. The aim would be to have fast queries and never consume lots of db space.

I've two options:

Have individual rows for every 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 buddies in a single row as CSV

    +----+-------------+-------------------+
    | ID | User_ID     | Friend_ID         |
    +----+-------------+-------------------+
    | 1  | 102         | 213,44,34,67,8    |
    | 2  | 64          | 23,33,45,105      |
    +----+-------------+-------------------+

When locating buddies I'm able to create an assortment using explode() however removing a person could be more difficult.

Edit: For second method I'd separate each id in array in php for functions for example counting yet others.

Which method do you consider is much better?

First technique is certainly better. It's why is relational databases great :)

It will help you to look for and group by a lot more specific criteria compared to second method.

Say you desired to create a question so customers often see who had them like a friend. The second method would need you to use within() and could be much reduced than using JOINS.

The very first technique is better in nearly every way. You won't just utilize your DBs indexes to locate records faster, it'll make modification far far simpler.

Make use of the energy from the relational database. Certainly opt for the very first approach. MySQL is faster than you believe, also it regularly handles Large datasets.

Breaking from first normal form is generally not desirable because

  1. Simple to Orpahned ids
  2. Simple to place invalid data types
  3. Updates can require full table scans
  4. Increases concurrency issues
  5. Not a way to produce the important thing (user_id, friend_id)