sql - 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.

question from:https://stackoverflow.com/questions/4895809/database-design-for-followers-and-followings

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