What is the logic of Following and Followers Systems?

Question

I'm woking on some project and I thinking about the follower and following system like twitter.I have members table on database.How can I do follower system.Whats is the logic?I worked about something like that: I created new table that name is following and i have 3 rows.These rows are id,user_id,follower_id. is that enough for me? I have not idea about how can do this system? Can you help me?

Answer 1

Yes, that design is sufficient for a simple following system such as that of Twitter. Of course, some extra data (such as the timestamp, as suggested by @Ina) could be useful and you don't strictly need the id in it.

I'd recommend something like:

userID INT PRIMARY,
followID INT PRIMARY,
creationTime DATETIME

Answer 2

"Follows" (or "is followed by") is a relationship between two user entities which your table reflects.

This means that unless you are planning to treat multiple follows and unfollows between the same users as separate entries, you don't even need the id.

Make the user_id and follower_id reference user with a FOREIGN KEY, make them both a composite primary key and create index on follower_id (to get a list of users one follows fast):

CREATE TABLE
        follows
        (
        user_id INT NOT NULL REFERENCES user (id),
        follower_id INT NOT NULL REFERENCES user (id),
        PRIMARY KEY
                (user_id, follower_id)
        KEY follower_id (follower_id)
        )
ENGINE=InnoDB -- if you want the foreign keys to work

You may add other attributes as well.

If you want to track follows and unfollows between the same users then you need to promote this into an entity, adding a surrogate primary key:

CREATE TABLE
        follows
        (
        id INT NOT NULL PRIMARY KEY,
        user_id INT NOT NULL REFERENCES user (id),
        follower_id INT NOT NULL REFERENCES user (id),

        -- These are additional attributes

        follows_from DATETIME NOT NULL,
        follows_till DATETIME,

        --

        KEY follower_id (follower_id),
        KEY user_id (user_id)
        )
ENGINE=InnoDB -- if you want the foreign keys to work