MySQL Database Design – Soft Delete Post Likes

best practicesdatabase-designMySQL

I have implemented a like/unlike features for posts in one of my projects. The like table is normalized and has a column visible. The schema is as follows:

Column      Type       Null    Default  Links to
id          int(11)     No           
user_id     int(11)     Yes     NULL    user  -> id      
post_id     int(11)     Yes     NULL    posts -> id      
visible     tinyint(1)  Yes     NULL         
created     datetime    No           
updated     datetime    No

What I'm doing currently is that when a user likes a post, I create a record and when he un-likes it, I just set the visible column to false. The count is calculated by post_id and visible = true. I did that to prevent excessive load on database when a user likes/unlikes a post.

My question is am I being paranoid in considering database performance so early (without much traffic) or is this among the best practices and I should continue with this?

Best Answer

You should consider the balance between how often your data will be accessed vs how often your data will be updated.

Adding another condition on your SELECT just to display your content may well create more load than deleting rows in the infrequent occasion that a user un-likes. Note that you will need an index on visible to efficiently query against it.

Related Question