database design for 'followers' and 'followings'?

Solution 1:

In general, your design is correct.

But, if user_id is unique in the table "users", you don't need the column "id" in "users". (A single table containing a unique "id" and a unique "user_id" is pretty unusual.) You also don't need the column "id" in the table "followers".

Primary key in "followers" should be (user_id, follower_id), and make sure each of those columns has a foreign key referencing "user_id" in "users".

Solution 2:

Here is my design.

Id(PK)  userId(FK)  followerId(FK)  followedDate         unfollowedDate
1       123         456             YYYY-MM-DD HH:MI:SS  YYYY-MM-DD HH:MI:SS
...     ...         ...             ...                  ...

I assumed userId - followerId combiation is unique. I can use them as composite key and remove Id from table. Dates can also be useful. For example, if user unfollows and follows again in 5 minutes, it doesn't generate notification - I assume user made it by mistake. I can also analyze following statistics by date.

Solution 3:

General tip. Use integers for ids rather than strings. There is a significant performance difference. So drop users.user_id, and rename users.id to users.user_id. Secondly your followers table should have indexes on user_id and follower_id. Again there is a significant performance benefit. I also like the idea of having a unique index on (user_id, follower_id), calling that your primary key, and dropping your id column.

Solution 4:

Yes, your design is the usual way of dealing with many-to-many relationships. Search for "modeling many-to-many database" and you will find lots of resources giving you examples of this.

Add foreign keys from your relationship table to the users table.

If your relationship involves additional information, you would put that as column in your connecting table. Maybe, for instance, the date when one user started following another.

A separate surrogate key in the connecting table, like the ID column you have added, can be useful if you will want to have other tables reference your table.