database design for 'followers' and 'followings'?

Question

Dear database experts/programmers:

I have a mysql table with users information, such as

id      user_id         name etc. 1       userA             2       userB    3       userC ..      .. ..      .. 

I want to create a feature like 'follow' other users something like twitter. A userA can follow userB, or userB can follow userA, or both can follow each other. For that purpose, should I create 1 table, lets say followers

id      user_id     follower_id 1           userA       userB 2           userC       userA 3           userA       userC 4           userB       userC 5           userX       userA 

Now I want to find who is following a userA. I'd so sth like: Select * from followers where user_id = userA This will select userB and userC. Thats what I need.

Now I want to find, which persons userA is following (for example in above table, userA is following userC and userX. Then I should run something like Select * from followers where follower_id=userA.

My question is that, is this database design correct for this problem (considering in mind database redundancy and optimization?) Or there can be better approach than this? Thanks.

Answer 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".