4

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

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:

https://stackoverflow.com/questions/25101089/mutual-friendship-rails-4

How to Implement a Friendship Model in Rails 3 for a Social Networking Application?

My question is which way is the best way as efficiency and speed? Using an array column or separate table?

Thank you.

Community
  • 1
  • 1
cyonder
  • 852
  • 1
  • 15
  • 36
  • Separate table ofcourse! – DirtyBit Sep 24 '15 at 15:19
  • 3
    Oh, no! Not another instance of non-normalized data in a database..... expect a stream of questions on how to work round all the issues that it raises – Mark Baker Sep 24 '15 at 15:19
  • Are you trying to do this with PHP or Ruby? – PHP Web Dev 101 Sep 24 '15 at 15:22
  • @PHPWebDev101 well, array column is developed in PHP but now I am re-writing entire back end in rails. So, whatever is the best way, I am gonna develop it in rails. – cyonder Sep 24 '15 at 15:25
  • @MarkBaker so you are suggesting me to use separate table? – cyonder Sep 24 '15 at 15:27
  • 1
    Yes I am suggesting that; comma-separated values in a column create a host of coding problems and performance issues that can be easily eliminated by normalising the data into a separate table – Mark Baker Sep 24 '15 at 15:46

1 Answers1

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.

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.

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.

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.

Martin Schmidt
  • 311
  • 2
  • 8