-2

I have created a basic like system for my system. The likes are stored in the below format

user_id, post_id, liked_at

I want to keep the total likes in the database (as it will help in getting total post likes when retrieving post lists) 

post_id, total_likes

I'm currently having 2 options for making these function.

  • Creating a view to generate post-wise total likes
  • Trigger to update total likes

what is the best way to go?

camille
  • 278
  • 1
  • 2
  • 20
  • 1
    This can't be answered without a definition of "best" & details you don't give, plus it is nevertheless a frequent duplicate, plus it shows no research. [Strategy for “Which is better” questions](https://meta.stackexchange.com/q/204461) See [ask], other [help] links & the voting arrow mouseover texts. Before considering posting please always google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. – philipxy Feb 14 '20 at 10:03
  • Does this answer your question? [Pros and Cons of Triggers vs. Stored Procedures for Denormalization](https://stackoverflow.com/questions/2088905/pros-and-cons-of-triggers-vs-stored-procedures-for-denormalization) – philipxy Feb 14 '20 at 10:04

2 Answers2

1

Since you will probably invoke the "LIKE" code from only one place in your code, it is a tossup:

  • Code it in your app code (either inline or in a subroutine)
  • Have STORED PROCEDURE Like(user_id, post_id)
  • Use a TRIGGER, then insert one place, and have it do the other insert/update
  • VIEW

The first two 'feel' better; I tend to avoid "side effects"; they bite me a year later after I have forgotten about them.

I guess my favorite is a subroutine or a stored proc -- this isolates all the actions for LIKEing in a single place; it helps cleanly structure the entire system.

Rick James
  • 135,179
  • 13
  • 127
  • 222
0

Personally when it is possible I prefer not to add redundant information. So I would prefere a view. If you add 'total_like' to the post table and keep it updated via trigger nothing (other than another trigger) would prevent someone to update directly that field and misalign the data). Only if it gets to slow I would consider the idea of a trigger. Hope it helps

AndreaM
  • 31
  • 4
  • 1
    In general, I agree with you. However, in a _very_ busy system, handling `LIKEs` and `CLICKs` can become a performance problem, and it may be necessary to "break the rules" to achieve acceptable performance. (Probably the OP's app is not there yet.) – Rick James Feb 15 '20 at 19:11
  • 1
    The "redundancy is bad" camp says that you can keep a list of LIKEs, but you must do a `COUNT(*)` to get the total likes. This is a common case where performance goes bad. Sometimes indexes help enough. Sometimes summary tables can come to the rescue, but that is just another form of "redundant info". – Rick James Feb 15 '20 at 19:14