Search code examples
mysqlselectjoinnormalization

Having a column 'number_of_likes' or have a separate column...?

In my project, I need to *calculate 'number_of_likes' for a particular comment*.

Currently I have following structure of my comment_tbl table:

id    user_id   comment_details
1     10        Test1
2     5         Test2
3     7         Test3
4     8         Test4
5     3         Test5

And I have another table 'comment_likes_tbl' with following structure:

 id    comment_id   user_id
  1       1         1
  2       2         5
  3       2         7
  4       1         3
  5       3         5

The above one are sample data.

Question :

On my live server there are around 50K records. And I calculate the *number_of_likes to a particular comment by joining the above two tables*. And I need to know Is it OK?

Or I should have one more field to the comment_tbl table to record the number_of_likes and increment it by 1 each time it is liked along with inserting it into the comment_likes_tbl....?

Doed it help me by anyway...?

Thanks In Advance.....

Solution

  • You can take a few different approaches to something like this

    1. As you're doing at the moment, run a JOIN query to return the collated results of comments and how many "likes" each has
    2. As time goes on, you may find this is a drain on performance. Instead you could simply have a counter that increments attached to each comment field. But you may find it useful to also keep your *comment_likes_tbl* table, as this will be a permanent record of who liked what, and when (otherwise, you would just have a single figure with no additional metadata attached)
    3. You could potentially also have a solution where you simply store your user's likes in the comment_likes_tbl, and then a cron task will run, on a pre-determined schedule, to automatically update all "like" counts across the board. Further down the line, with a busier site, this could potentially help even out performance, even if it does mean that "like" counts lag behind the real count slightly.

    (on top of these, you can also implement caching solutions etc. to store temporary records of like values attached to comments, also MySQL has useful caching technology you can make use of)

    But what you're doing just now is absolutely fine, although you should still make sure you've set up your indexes correctly, otherwise you will notice performance degradation more quickly. (a non-unique index on comment_id should suffice)