3

I want to implement like/dislike system, but I don't know how to achieve best performance. I have to tables: blog posts and comments that need rating.

  • Should I store all data in one rating table? Or should I create separate tables for posts and for comments?
  • Should I store total result into one record in post table or should I query for all +1 click in that rating tables? I want one person to rate on comment/post just one time.
  • Should I mark if person already voted for comment or post or is it just damage performance?

I haven't launch big systems, but looking at the same projects I think there will be 15 000 hits a day but running on average VPS with 1-2Gb of RAM, 1-2 Cores and without SSD. It could be fast upgraded to much more powerful dedicated server but it's too expensive.

closed as too broad by Arseni Mourzenko, user40980, durron597, GlenH7, user22815 Jun 7 '15 at 16:03

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    You should in any case store the number of likes and dislikes separately. This gives you much more information than just storing the balance. Example: if a score of +1 consists of one like only, the post was not very interesting. If that same +1 was the sum of 100 dislikes and 101 likes, it was controversial. – Jan Doggen Jun 5 '15 at 7:44
  • @JanDoggen No, I mean should I store total number of likes and dislikes separately (100 and 101 values in one row, incrementing on voting) or should I query 201 votes records for post/comment. – Dracontis Jun 5 '15 at 9:14
  • I'd store both the individual votes and the denormalized totals (and don't make the stackexchange mistake of storing only upvotes - downvotes instead of both). – CodesInChaos Jun 5 '15 at 10:14
7

You have to have knowledge of whether any given user has voted on any given post and on any given comment. This means that you have to introduce a new user_post_votes table with a user_id column and a post_id column, as well as a user_comment_votes table with a user_id column and a comment_id column.

If it is only possible to upvote, then the presence of a row in each of these tables means that the user with the given user_id has upvoted the post or comment with the given id. However, if it is possible to either upvote or downvote, then you will need one more column to stand for "upvote" or "downvote". The best type for that column is actually INTEGER, with valid values being either -1 or +1, so that SELECT SUM(vote) FROM user_post_votes WHERE post_id = X will give you the total votes for a post, taking into account the downvotes. (TINYINT might also do, but I am not sure if it will be correctly summed into an integer if there are more than 128 upvotes, you will need to check that.)

Should I store all data in one rating table? Or should I create separate tables for posts and for comments?

I shall presume that in fact you already have separate tables for posts and for comments, and what you actually meant to ask with the above question was whether you should also create separate tables for votes on posts and for votes on comments. Yes, you should. And if you tried doing anything else, you would very quickly find out that nothing else makes sense. Posts have to be identified by a post_id column in a user_post_votes table, and comments have to be identified by a comment_id column in a user_comment_votes table. There is no way around that. You cannot have a column which contains an id which in some cases refers to one table, and in some cases refers to another table. (Technically you could, but such a thing would be in blatant violation of relational database theory, against all best practices, and practically a nightmare to work with.)

Should I store total result into one record in post table or should I query for all +1 click in that rating tables? I want one person to rate on comment/post just one time.

Do not store the total result anywhere. First, complete the development of your system and bring it to a fully working state by recomputing the vote count every time it is needed, as I have already shown. Then, if and only if there appears to be a performance problem, and if and only if you can prove that the performance problem is located in this computation, then go optimize that part by caching the total vote count for each post. Most chances are, you will not need to.

Should I mark if person already voted for comment or post or is it just damage performance?

You cannot just "mark" if a person has already voted, because you have N persons, X comments, and Y posts. This is a many-to-many relationship, so as I have already explained, you have to have a user_comment_votes table and a user_post_votes table. Whether it hurts performance or not is inconsequential, since you have already established that this information is necessary. So, if it is necessary, then you will need to keep track of it.

Not the answer you're looking for? Browse other questions tagged or ask your own question.