Followers/following database structure

Question

My website has a followers/following system (like Twitter's). My dilemma is creating the database structure to handle who's following who.

What I came up with was creating a table like this:

 id  |  user_id  |  followers |  following
  1  |    20     |  23,58,84  |  11,156,27
  2  |    21     |  72,35,14  |  6,98,44,12
 ... |   ...     |    ...     |     ...

Basically, I was thinking that each user would have a row with columns for their followers and the users they're following. The followers and people they're following would have their user id's separated by commas.

Is this an effective way of handling it? If not, what's the best alternative?

Answer 1

That's the worst way to do it. It's against normalization. Have 2 seperate tables. Users and User_Followers. Users will store user information. User_Followers will be like this:

id | user_id | follower_id
1  | 20      | 45
2  | 20      | 53
3  | 32      | 20

User_Id and Follower_Id's will be foreign keys referring the Id column in the Users table.