Database design and modeling for Following Feature
Sat 04 Jan 2020

In order to design this function we need to know self-join technique first. Now let's have a look.

1. Self-join

SELECT employees.*, managers.name as manager FROM users as employees
JOIN users as managers
ON employees.manager_id = managers.id

Result:

Same above result using WHERE clause instead.

SELECT employees.*, managers.name as manager
FROM users as employees, users as managers
WHERE employees.manager_id = managers.id

Play it yourself here.

It's easy to imagine that we have a pillar that is manager_id which would join back to primary key to pull left columns to right (manager column as we can see on above picture). That is the main idea.

In Ruby on Rails.

class User < ApplicationRecord
  has_many :employees, class_name: 'User', foreign_key: 'manager_id'
end
# User.first.employees

2. Following feature

Using above technique we can do like this.

id follower_id
1 2
2 3
3 4

User id 1 followed by user id 2 but id is unique so we can't make 3 follows 1 - one more relation.

id follower_id
1 2
1 3
3 4

Now we think about many-to-many relation. Thus, we create Follows table to contain relation like that.

USERS TABLE    
FOLLOWS TABLE
   
id name   id user_id following_user_id
1 will   1 1 2
2 truong   2 1 3
3 batman        

Note that we are doing a crucial step in problem solving "Explore concrete examples".

follows.id is not what we care about but user_id and following_user_id. This is a bit different because last time we use users.id primary key instead. We can reach that aim by make relation between users and follows tables. By joining them on:

users.id = follows.following_user_id

Unluckily, users don't just follow other users but products, brands etc. So we use polymorphic relation for FOLLOWS table.

FOLLOWS TABLE
     
id user_id source_id source_type
1 1 2 User
2 1 3 User
3 1 1 Product
4 1 2 Product
5 1 1 Brand
6 1 2 Brand

Now a user can follow other users, products, brands. 

user.follows.where(source_type: 'User')
user.follows.where(source_type: 'Product')
user.follows.where(source_type: 'Brand')

3. Refactor

We want to express that a user has many following_users through follows. So what we can do with rails?

# id
# name:         string
#
class User < ApplicationRecord
  has_many :follows
  has_many :following_users, through: :follows, source: :source, source_type: 'User'
end

3.1 Listing following users

There are two ways to do this.

  1. Using above polymorphism so that we don't need to define following_users because it is defined in User model as source name. 
    # id
    # name:         string
    #
    class User < ApplicationRecord
      has_many :follows
      has_many :following_users, through: :follows, source: :source, source_type: 'User'
    end
    

    Extra Joined infos for following_users and follows.

    source: :source, source_type: 'User'

    Using follows as bridge. source and source_type for defining characters of following_users table in order to let Follow know how to join.

    # id
    # used_id
    # source_id :   int
    # source_type:  string
    class Follow < ApplicationRecord
      belongs_to :source, :polymorphic => true
      belongs_to :user
    end​

    Follows table contain two information: users and following users corresponding user_id and source_id. From these foreign keys we make two joins statements to User for information of users and following users.

  2. If we don't use polymorphism. Defining class_name and foreign_key for class's table (User) to get to know key to join. 
    # id
    # name:         string
    #
    class User < ApplicationRecord
      has_many :follows
      has_many :following_users, through: :follows
    end
    ​
    # id
    # used_id
    # source_id :   int
    # source_type:  string
    class Follow < ApplicationRecord
      belongs_to :source, :polymorphic => true
      belongs_to :user
      belongs_to :following_users, class_name: 'User', foreign_key: 'following_id'
    end
    ​

3.2 Listing followers

  1. Join source_id instead of user_id (opposite direction).
    has_many :followings, class_name:'Follow', as: :source
    has_many :followers, through: :followings

    Followings is a bridge. 

    class User < ApplicationRecord
      has_many :followings, class_name:'Follow', as: :source
      has_many :followers, through: :followings
    end​

    Then trace back to User as followers in Follow model:

    class Follow < ApplicationRecord
      belongs_to :source, :polymorphic => true
      belongs_to :user
    
      belongs_to :followers, class_name: 'User', foreign_key: 'user_id'
    end

    Extra joined infos for following_users and follows is put at Follow model:

    class_name: 'User', foreign_key: 'user_id'

    We use followers name instead users so that we have to declare foreign_key for it.


Doing same way for Product, Brand etc.

user_id source_id source_type
111 88 ProductCategory
111 2041 Product
111 212 Brand
111 232 User

You can check out this code to play with it :D 

Thank for reading!