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
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.