How to design "like on posts" in relational databases?

I'm developing a kind of social app, where an user can write a post, and give like on other posts.

And on the frontend users can see how many likes a post has.

In this case, how can I handle the count of likes for each post?

What I've been thinking so far is...

(1) maintain the post_likes table whose primary keys are user_id and post_id. Everytime users give like on a post, a new row is appended on the table. The count of likes are calculated from counting the corresponding rows of post_likes table.

(2) posts table has a like_count column. Everytime users give like on a post, the value of the column increases by 1. The count of likes are calculated from selecting like_count value.

I think that the implementation (1) would be better in that which user gave like to which post. However, it seems that it could be inefficient since select COUNT(*) should be executed in order to get the like count whenever users requests a page.

what is the best approach in this case?

Solution

Both approaches have their pros and cons, but I think approach (1) is a better option for several reasons:

You can prevent users from liking their own posts (if you want to implement that).
You can allow users to remove their like.
You can prevent one user liking a post multiple times.

If performance is an issue you can opt for a new table post_likes_count that will periodically be updated from post_likes with the new like count for a post. Again, keep in mind it depends on what you specifically want to accomplish.