Implementing count model in database

Question

I've run into bit of an issue on implementing a count system in my project.

I have a system where users can like a post. Should I keep a count of it in the same table like

or should I remove the like_count column from the post table.

My concern if I have like_count column in the post table is -- frequent update of the value.

If I choose not to add a like_count column in the post table, I'd have to use count() frequently to display the details. I've to design a data model for thousands of users. So which model is the right way to implement?

P.S: Please ignore the data types of the columns. I just did a quick version of it to post it here.

Answer 1

First:

• In the diagram post_id is the PK of PostLikes forcing any post to have only one like. PK should be (post_id,user_id)
• Table names should be singular, so PostLikes should be named POST_LIKE.

That said, my recommendation is:

A like counting feature is not a critical one (like, say the balance of a bank account) -- meaning, it is not critical to have the very exact like count of a post at any given second -- so in that case I suggest you to keep the LIKE table as well as the like_count column in POST and update it every N minutes. You don't even have to update every post's like count every N minutes. Every run of the batch process can update the count of a group of posts or the most recently liked ones.

Please note that with such a column you are knowingly de-normalizing for performance's sake, which is OK if well sustained.

Answer 2

Don't optimize ahead of time.

Essentially you are trading a "Count" for more frequents writes to your "Post" table and having to keep things in sync. I would remove the "like_count" column for now and write System tests to profile your applications performance.

If it becomes a performance problem you can optimize at that point.

EDIT: Didn't realize it was already a performance problem.

From a performance point of view there is just not enough information here to say which would perform better overall.

EDIT2: changed my mind

From a design point of view it makes more sense keeping it on the "posts" table if you are unable to optimize it away.