Data structure for Twitter-like follow

Need help Database
1

I’m trying to implement a “follow” system, and my first inclination was to create a Following list field of type User on the User object. That would make it simple to list the people being followed BY the current user.

However, I’d also like to list the people following the current user, which suggests the query would be something like “Search Users where Following contains Current User”. From a performance standpoint, would I be better off creating a many-to-many relationship using a Data Type (table) called Follows, which would link the follower to the user being followed?

The first approach seems a bit more intuitive, but which would yield the best performance?

2

I tend to have two lists on the User.

“Users I follow”
“Users who follow me”

3

Thanks for the info, @NigelG. So that suggests that whenever someone follows or unfollows someone, two users are updated - the follower and the followee, right?

4

Yes, twitter is different as it is “two sided”.

So when you follow/unfollow someone, I update both users.

5

Out of curiosity and for future reference, does that approach offer a performance advantage over creating a separate many-to-many “thing” which would presumably involve updating just a single record per follow/unfollow action?

6

At normal levels of use, I doubt it. Most people won’t be following millions of people.

But for a different use case with large numbers it may be faster.

The question I would be asking myself is around adding further information to the link. And the volatility of the information.