For storing friends relationships in social networks, is it better to have another table with columns relationship_id, user1_id, user2_id, time_created, pending or should the confirmed friend's user_id be seralized/imploded into a single long string and stored along side with the other user details like user_id, name, dateofbirth, address and limit to like only 5000 friends similar to facebook?
为了在社交网络中存储朋友关系，最好是另一个表包含列relationship_id，user1_id，user2_id，time_created，pending，或者是否应将已确认朋友的user_id分解/内爆为单个长字符串并与其他用户详细信息一起存储喜欢user_id，名称，dateofbirth，地址和限制，只喜欢5000个类似facebook的朋友？
Are there any better methods? The first method will create a huge table! The second one has one column with really long string...
有没有更好的方法？第一种方法将创建一个巨大的表！第二个有一列非常长的字符串......
On the profile page of each user, all his friends need to be retrieved from database to show like 30 friends similar to facebook, so i think the first method of using a seperate table will cause a huge amount of database queries?
在每个用户的个人资料页面上，需要从数据库中检索所有他的朋友，以显示类似于Facebook的30个朋友，所以我认为使用单独表格的第一种方法会导致大量的数据库查询？
2 个解决方案
#1
12
The most proper way to do this would be to have the table of Members (obviously), and a second table of Friend relationships.
最合适的方法是拥有会员表（显然），以及第二个朋友关系表。
You should never ever store foreign keys in a string like that. What's the point? You can't join on them, sort on them, group on them, or any other things that justify having a relational database in the first place.
你永远不应该将外键存储在这样的字符串中。重点是什么？您不能加入它们，对它们进行排序，对它们进行分组，或者首先证明拥有关系数据库的任何其他东西。
If we assume that the Member table looks like this:
如果我们假设Member表看起来像这样：
MemberID int Primary Key
Name varchar(100) Not null
--etc
Then your Friendship table should look like this:
然后您的友谊表应如下所示：
Member1ID int Foreign Key -> Member.MemberID
Member2ID int Foreign Key -> Member.MemberID
Created datetime Not Null
--etc
Then, you can join the tables together to pull a list of friends
然后，您可以将表连接在一起以提取朋友列表
SELECT m.*
FROM Member m
RIGHT JOIN Friendship f ON f.Member2ID = m.MemberID
WHERE f.MemberID = @MemberID
(This is specifically SQL Server syntax, but I think it's pretty close to MySQL. The @MemberID is a parameter)
（这特别是SQL Server语法，但我认为它非常接近MySQL。@ MemberID是一个参数）
This is always going to be faster than splitting a string and making 30 extra SQL queries to pull the relevant data.
这总是比分割字符串和进行30次额外的SQL查询以提取相关数据更快。
#2
0
Separate table as in method 1. method 2 is bad because you would have to unserialize it each time and wont be able to do JOINS on it; plus UPDATE's will be a nightmare if a user changes his name, email or other properties.
如方法1中那样单独的表。方法2很糟糕，因为每次都必须对它进行反序列化，并且不能对它进行JOINS;如果用户更改其姓名，电子邮件或其他属性，加上UPDATE将是一场噩梦。
sure the table will be huge, but you can index it on Member11_id, set the foreign key back to your user table and could have static row sizes and maybe even limit the amount of friends a single user can have. I think it wont be an issue with mysql if you do it right; even if you hit a few million rows in your relationship table.
确保表格很大，但您可以在Member11_id上对其进行索引，将外键设置回用户表，并且可以具有静态行大小，甚至可以限制单个用户可以拥有的朋友数量。我认为如果你做得对，它不会成为mysql的问题;即使你在关系表中打了几百万行。
#1
12
The most proper way to do this would be to have the table of Members (obviously), and a second table of Friend relationships.
最合适的方法是拥有会员表（显然），以及第二个朋友关系表。
You should never ever store foreign keys in a string like that. What's the point? You can't join on them, sort on them, group on them, or any other things that justify having a relational database in the first place.
你永远不应该将外键存储在这样的字符串中。重点是什么？您不能加入它们，对它们进行排序，对它们进行分组，或者首先证明拥有关系数据库的任何其他东西。
If we assume that the Member table looks like this:
如果我们假设Member表看起来像这样：
MemberID int Primary Key
Name varchar(100) Not null
--etc
Then your Friendship table should look like this:
然后您的友谊表应如下所示：
Member1ID int Foreign Key -> Member.MemberID
Member2ID int Foreign Key -> Member.MemberID
Created datetime Not Null
--etc
Then, you can join the tables together to pull a list of friends
然后，您可以将表连接在一起以提取朋友列表
SELECT m.*
FROM Member m
RIGHT JOIN Friendship f ON f.Member2ID = m.MemberID
WHERE f.MemberID = @MemberID
(This is specifically SQL Server syntax, but I think it's pretty close to MySQL. The @MemberID is a parameter)
（这特别是SQL Server语法，但我认为它非常接近MySQL。@ MemberID是一个参数）
This is always going to be faster than splitting a string and making 30 extra SQL queries to pull the relevant data.
这总是比分割字符串和进行30次额外的SQL查询以提取相关数据更快。
#2
0
Separate table as in method 1. method 2 is bad because you would have to unserialize it each time and wont be able to do JOINS on it; plus UPDATE's will be a nightmare if a user changes his name, email or other properties.
如方法1中那样单独的表。方法2很糟糕，因为每次都必须对它进行反序列化，并且不能对它进行JOINS;如果用户更改其姓名，电子邮件或其他属性，加上UPDATE将是一场噩梦。
sure the table will be huge, but you can index it on Member11_id, set the foreign key back to your user table and could have static row sizes and maybe even limit the amount of friends a single user can have. I think it wont be an issue with mysql if you do it right; even if you hit a few million rows in your relationship table.
确保表格很大，但您可以在Member11_id上对其进行索引，将外键设置回用户表，并且可以具有静态行大小，甚至可以限制单个用户可以拥有的朋友数量。我认为如果你做得对，它不会成为mysql的问题;即使你在关系表中打了几百万行。