CODEWITHSUNDEEP
sqldatabase
tcj
tcj

Reputation: 621

database design for 'followers' and 'followings'?

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.

Upvotes: 53

Views: 30523

Answers (4)

Iren Saltalı
Iren Saltalı

Reputation: 536

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.

Upvotes: 13

Andreas Vendel
Andreas Vendel

Reputation: 746

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.

Upvotes: 3

btilly
btilly

Reputation: 46389

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.

Upvotes: 10

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95522

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

Upvotes: 25

Related Questions