Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 985
CyberJunkie Avatar asked Jul 09 '11 00:07

CyberJunkie


2 Answers

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.

like image 113
AlienWebguy Avatar answered Oct 08 '22 21:10

AlienWebguy


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.

like image 27
dqhendricks Avatar answered Oct 08 '22 19:10

dqhendricks