The best database architecture for friendships in a social networking application

Question

I am working on a social networking site in Rails, and currently I can present a couple of ways that I can make friendships (just like Facebook). But since many of the application’s functions depend on this part of the application, I’m a little afraid to do one thing, and then I understand that I could do it better, and then do migrations and change the whole thing one by one.

If you guys can suggest which one works best, that would be great. Also, if none of my architectures are good enough, feel free to suggest a new one.

ARCHITECTURE 1

One friendship table in which there are three columns -

• user_id (identifier of the user who sends the request)
• friend_id (identifier of the user who receives the request)
• pending (boolean to save state. False if the request was accepted, true otherwise)

To get all user friends

  def friends
    user_ids = Friendship.where(
      '(user_id = :id OR friend_id = :id) AND pending = false',
       id: self.id
    ).pluck(:user_id, :friend_id)
     .flatten
     .uniq
     .reject { |id| id == self.id }

    users = User.where(id: user_ids)
  end

To receive all friend request received by user -

def friend_requests
  friend_ids = Friendship.where('friend_id = ? AND pending = true', self.id).all
  users = User.where(id: friend_ids)
end

The benefits I can think of are

• Requires a minimum number of tables and records in the database compared to other architectures. I am not sure if this is an advantage or not, since I heard that the number of records in a table does not affect performance. So correct me if I am wrong.
• Only a few checks are required. First, user_id will be unique in the friend_id scope. Secondly, user_id is not equal to friend_id.
• . ( - ). Un-friending .

, -

• User.friends User.friend_requests , ActiveRecord. , , .

2

. - -

• user_id ( )
• friend_id (id )

• user_id ( )
• friend_id (id )

-

has_many :friendships,
  class_name: "Friendship",
  foreign_key: :user_id,
  primary_key: :id

has_many :friends,
  through: :friendships,
  source: :friend

-

has_many :friend_requests,
  class_name: "FriendRequest",
  foreign_key: :friend_id,
  primary_key: :id

, , -

• User.friends User.friend_requests . , , User.friends

, , -

• . User.friends, 2 friendhsip.

---

| id  | user_id | friend_id |  
-----------------------------
|  1  |   15    |    30     |
-----------------------------
|  2  |   30    |    15     |
-----------------------------

• , : 2 ( ), 1 .
• .
  • , , , , / 3 , .

, -

() ( 1) ( 2)?

, 2 ? ( 2)

Answer 1

, friend_request friendship. , , .

: -

• ( ", " ).
• , , , .
• , ( )

, , . .

, ? .

. :

• //, ,
• ,

( ) : :

• //, .
• , .