Let’s say you’re designing a social media app and you’ll use a SQL database and it needs to be scalable. Users should be able to post, like, comment and follow others. When talking specifically about likes, my first thought would be to have a table for likes:
| like_id | user_id | post_id | timestamp |
|---|---|---|---|
| 1 | 1 | 1 | datetime |
| 2 | 2 | 2 | datetime |
where “user_id” is a foreign key referencing the Users table.
So, if I wanted to, e.g. get all users that liked a particular post, I could run a query like so: SELECT user_id from Likes WHERE post_id = {{postID}};
However, if this app scales and has millions of users, this Likes table would be gigantic, as it would store all likes for all users in the app. So, what would be the best way to make this scalable?
Is indexing the Likes table on user_id and post_id enough? considering the scale we’re looking at?
lucky is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.