designing a follower/following schema between two tables in SQL?

Question

suppose we have two tables:

Brands
Users

i want to design a system where:

Brands can follow Brands
Brands can follow Users
Users can follow Brands
Users can follow Users

this is what i have done so far:

ed_id: user_id of the followed
er_id: user_id of the follower

note: the two tables are different but i haven't written all the attributes to keep things simple

while this works fine among the Users and i can return the followers for each user with some simple queries, i don't know how to establish a relationship between Users and Brands so that they can follow each other.

any help is appreciated!

You can add a super type "Followers". Search for "sql subtyping" a lot of info to find. — Serg, Aug 23 '21 at 08:31
Your `Brands` and `Users` tables looks like the tables which stores the instances of the same entity. If so then they must be combined into one table, and the following data will be single self-reference (if an instance may follow only one instance) or junction table (if an instance may follow and be followed by more than one instance). — Akina, Aug 23 '21 at 08:49
@Akina i haven't shown all the pieces of data in the Brands table on purpose, otherwise there is a lot of difference between the two. — Ebdulmomen1, Aug 23 '21 at 09:00
It doesn't change anything. Is there any reason to treat brands and users as separate entities? — Akina, Aug 23 '21 at 09:04

score 1 · Accepted Answer · answered Aug 23 '21 at 08:51

There are a few ways to do this.

It seems that "user" and "brand" are very similar. They share many attributes - in fact, if you replace "brandname" and "username" with "name", they'd be identical.

This could lead you to a range of different designs.

I you think "actually, there are lots of differences, I've just left them out to keep the question simple", you might say that there is a superclass called User, and two subclasses called Brand and Person. There are several ways to model inheritance in relational databases. You might then have a model as follows:

Users
----- 
User_id (pk)
hashed_password
salt
phonenumber
address

Person
------
person_id (pk)
user_id (fk)
person_name
....


Brand
-----
brand_id (pk)
user_id (fk)
brand_name
....

Follow
-----
user_id_follower (fk)
user_id_followee (fk)

However, you might conclude that there is no meaningful semantic or behavioural difference between "person" and "brand" - that the type of user is merely an attribute of that user. In that case, life gets even easier:

Users
----- 
User_id (pk)
hashed_password
salt
phonenumber
address
name
user_type ('brand', 'person')

Follow
-----
user_id_follower (fk)
user_id_followee (fk)

the first case works for me! thank you a ton. – Ebdulmomen1 Aug 23 '21 at 09:09 — Ebdulmomen1, Aug 23 '21 at 09:09

score 1 · Answer 2 · answered Aug 23 '21 at 08:51

You can base on next tables schema:

-- all entities table
create table followers (
    id int primary key auto_increment,
    follower_type enum('user', 'brand')
);

-- users data extention
create table users (
    id int primary key,
    user_name varchar(255),
    foreign key (id) references followers(id)
);

-- brands data extension
create table brands (
    id int primary key,
    brand_name varchar(255),
    foreign key (id) references followers(id)
);

-- following rules
create table follow_rules (
    id int,
    follow_to int,
    unique key (id, follow_to)
);

Test SQL here

designing a follower/following schema between two tables in SQL?

2 Answers2