Database Design for 'Followers' and 'Followings'

Followers/following database structure

That's the worst way to do it. It's against normalization. Have 2 seperate tables. Users and User_Followers. Users will store user information. User_Followers will be like this:

id | user_id | follower_id
1  | 20      | 45
2  | 20      | 53
3  | 32      | 20

User_Id and Follower_Id's will be foreign keys referring the Id column in the Users table.

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

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)

Best practice to build a followers/following MySQL database

Short answer: 10,000 is so few that any design will be "good enough".

Long answer: For more scaling, consider the following...

These designs are usually bad practice:

• two tables in a 1:1 relationship.
• store something that can be computed.

I say "usually" because you are reaching into cases where exceptions are warranted. But first, let me mention some other schema designs:

CREATE TABLE Follow (
    er ...,  -- user id of the the follower
    ed ...,  -- user id of the the followed
    PRIMARY KEY(er, ed),
    INDEX(ed, er)
) ENGINE=InnoDB;

SELECT COUNT(*) FROM Follow WHERE ed = ?; -- number of followers for `ed`.
SELECT er FROM Follow WHERE ed = ?  -- list of such followers
(Similarly for the flip direction)

Notes:

• No surrogate AUTO_INCREMENT, since there is a perfectly good PK. And the queries will run faster, as we will see in a minute.
• Until you have 100K followers, the COUNT query is "fast enough" so that you don't need to precompute counts.

If you were to count the number of "Likes", it would be prudent to have a separate table for that frequently updated value. Such a table would be 1:1 with the User table, thereby violating the first bad practice. The justification here is to separate the very high write activity in Like from the low, but important read activity in the rest of the "user" info.

Twitter style following-follower table in SQL

With this query to find if who you follow, follow you too.

SELECT ff1.follower_id as followedBy,
(
   select count(follower_id) 
   from follower_following as ff2 
   where ff2.user_id = ff1.follower_id 
   and ff2.follower_id = ff1.user_id 
) as areWeFollowing 
FROM follower_following as ff1  
where user_id = 'userB';

Django models: database design for user and follower

The 'self' argument won't work unless you have a model called self.

Assuming that your assignment model is called Following, and you're using the built in User model then you can do:

class Following(models.Model):
    target = models.ForeignKey('User', related_name='followers')
    follower = models.ForeignKey('User', related_name='targets')

This will likely need some further uniqueness and validation logic.

Note the related_name attribute, see https://docs.djangoproject.com/en/1.10/ref/models/fields/#django.db.models.ForeignKey.related_name. This means that for a given user object you can do user.targets.all() to get users they follow, and user.followers.all() to get users who follow them.

Note also that Django returns target model instances, not IDs, in the ORM. This means that even though the underlying table may be called follower_id, in the python code following.follower will return an actual User object.

Prisma following/follower relationship schema

Here's the way I'd suggest modeling your schema.

model User {
  id        String  @id @default(autoincrement())
  username  String
  followers Follows[] @relation("following")
  following Follows[] @relation("follower")
}

model Follows {
  follower    User @relation("follower", fields: [followerId], references: [id])
  followerId  String
  following   User @relation("following", fields: [followingId], references: [id])
  followingId String

  @@id([followerId, followingId])
}

Changes

1. User table has two relation fields instead of one.
2. followerId and followingId are made mandatory. It doesn't really make sense to have a Follows relation table when either of those are absent. (You can't have a following relationship without one user following and one user to follow).
3. @@id([followerId, followingId]) represents the primary key in Follows table. A separate id field is redundant.
4. Changed field names to camelCase, which is the recommended convention in Prisma.

4 is optional ofcourse, but I'd suggest following it none the less.

You can find more details about this in the many-to-many subsection of the self-reation article in the Prisma doc.

---

Related Topics