许多数据库行与一个逗号分隔值行 [英] Many database rows vs one comma separated values row

问题描述

我创建了一个表格，允许网站用户成为朋友。我试图确定哪个是最好的表设计存储和返回用户的朋友。目标是具有快速查询并且不占用大量的db空间。

我有两个选项：

每个友谊都有不同的行。

  + ---- + ------------- + -------------- ----- + 
 | ID | User_ID | Friend_ID | 
 + ---- + ------------- + ------------------- + 
 | 1 | 102 | 213 | 
 | 2 | 64 | 23 | 
 | 3 | 4 | 344 | 
 | 4 | 102 | 2 | 
 | 5 | 102 | 90 | 
 | 6 | 64 | 88 | 
 + ---- + ------------- + ------------------- + 
  
 
 
 
或将所有朋友列为CSV 
  + ---- + ------------- + ------------------- + 
 | ID | User_ID | Friend_ID | 
 + ---- + ------------- + ------------------- + 
 | 1 | 102 | 213,44,34,67,8 | 
 | 2 | 64 | 23,33,45,105 | 
 + ---- + ------------- + ------------------- + 
  
当检索朋友时，我可以使用 explode（）创建一个数组用户会更棘手。 
 
 
 
 编辑：对于第二种方法，我将在php中将数组中的每个id分隔为计数和其他函数。
 
 
 
你认为哪种方法更好？
解决方案
这是什么使关系数据库的伟大：）
 
 
 
它将允许您搜索和分组比第二种方法更具体的标准。
 
 
 
说你想写一个查询，以便用户可以看到谁是他们的朋友。第二种方法要求你使用IN（），并且比使用JOINS慢得多。
 
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.

                        
这篇关于许多数据库行与一个逗号分隔值行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！