I have developed a friendship system, where users table have a friendship column. In this column, I store user's id in an array by separating with a coma. Example: 1,5,15,20
我开发了一个友谊系统，其中users表有一个友谊列。在本专栏中，我通过用昏迷分隔将用户的id存储在数组中。示例：1,5,15,20
So it means, user is friend with other users, who has 1,5,15,20 as id. But there is another way to do this. Creating a friendship table and store everything there. Like in this examples:
所以这意味着，用户是其他用户的朋友，他的身份是1,5,15,20。但还有另一种方法可以做到这一点。创建友谊表并将所有内容存储在那里。就像在这个例子中：
https://*.com/questions/25101089/mutual-friendship-rails-4
https://*.com/questions/25101089/mutual-friendship-rails-4
How to Implement a Friendship Model in Rails 3 for a Social Networking Application?
如何在Rails 3中为社交网络应用程序实现友谊模型？
My question is which way is the best way as efficiency and speed? Using an array column or separate table?
我的问题是哪种方式是效率和速度的最佳方式？使用数组列还是单独的表？
Thank you.
谢谢。
1 个解决方案
#1
1
Efficiency and speed depend on the way you use this data.
1# You have a model User and a column 'friends' with user_ids of friends in the same table.
This is fast to read and needs not much storage space. But you have to decide in whose table row the data should be stored, because you absolutely don't want to store/manage the same data in two different table rows. If you just need a method like User.friends this is a fast and efficient way to store the ids. But if you would like to have a methods like User.is_friends_with you have to query every row in your user table. That wouldn't be neither fast nor efficient.
这是快速阅读，不需要太多的存储空间。但是您必须决定应该在哪个表行中存储数据，因为您绝对不希望在两个不同的表行中存储/管理相同的数据。如果您只需要像User.friends这样的方法，这是一种快速有效的存储ID的方法。但是如果你想拥有像User.is_friends_with这样的方法，你必须查询用户表中的每一行。这既不快也不高效。
2# You have a model User and a column 'is_friends_with' with user_ids of friends in the same table.
Same as #1, but User.is_friends_with would be fast and User.friends would query all rows in the table.
与＃1相同，但User.is_friends_with会很快，User.friends会查询表中的所有行。
3# You have a model User and a model Friendship
This is fast and efficient if you want to query friendships in both directions. It's also possible to extend the Friendship model to store some other data with it (e.g. since when the users are friends or where they know each other from). But if a user has 100 friends you need 100 rows your friendships table.
如果您想在两个方向查询友谊，这是快速有效的。还可以扩展友谊模型以存储其他一些数据（例如，当用户是朋友或他们彼此认识的时候）。但是如果一个用户有100个朋友，你需要100行你的友谊表。
If you don't know yet what to do with all the friendships, you should go with #3. It's the most flexible way to do it and it's easy to extend later on.
如果你还不知道如何处理所有友谊，你应该选择＃3。这是最灵活的方式，以后很容易扩展。
#1
1
Efficiency and speed depend on the way you use this data.
1# You have a model User and a column 'friends' with user_ids of friends in the same table.
This is fast to read and needs not much storage space. But you have to decide in whose table row the data should be stored, because you absolutely don't want to store/manage the same data in two different table rows. If you just need a method like User.friends this is a fast and efficient way to store the ids. But if you would like to have a methods like User.is_friends_with you have to query every row in your user table. That wouldn't be neither fast nor efficient.
这是快速阅读，不需要太多的存储空间。但是您必须决定应该在哪个表行中存储数据，因为您绝对不希望在两个不同的表行中存储/管理相同的数据。如果您只需要像User.friends这样的方法，这是一种快速有效的存储ID的方法。但是如果你想拥有像User.is_friends_with这样的方法，你必须查询用户表中的每一行。这既不快也不高效。
2# You have a model User and a column 'is_friends_with' with user_ids of friends in the same table.
Same as #1, but User.is_friends_with would be fast and User.friends would query all rows in the table.
与＃1相同，但User.is_friends_with会很快，User.friends会查询表中的所有行。
3# You have a model User and a model Friendship
This is fast and efficient if you want to query friendships in both directions. It's also possible to extend the Friendship model to store some other data with it (e.g. since when the users are friends or where they know each other from). But if a user has 100 friends you need 100 rows your friendships table.
如果您想在两个方向查询友谊，这是快速有效的。还可以扩展友谊模型以存储其他一些数据（例如，当用户是朋友或他们彼此认识的时候）。但是如果一个用户有100个朋友，你需要100行你的友谊表。
If you don't know yet what to do with all the friendships, you should go with #3. It's the most flexible way to do it and it's easy to extend later on.
如果你还不知道如何处理所有友谊，你应该选择＃3。这是最灵活的方式，以后很容易扩展。