Use one table and a "type" column to represent two types of vote_counter or use separate table?

Question

I am building a blog system, the blog can have its comment. And like to provide the upvote/downvote feature for both the blog and comment. Since the vote count number of blog/comment should be persisted, i choose to use MySQL to act as the data store.

It seems that i have two choice, the first one is, use a general table and a type column to represent these two kinds of counter, like:

vote_counter
------------
entity_id   bigint          // the id of blog or comment, depends on the "type" column value
type        tinyint         // for example, 1 for blog and 2 for comment
count       integer

Another option is to use one table for one "voteAble" type, for example:

blog_vote_counter          comment_vote_counter
-----------------          --------------------
blog_id    bigint          comment_id    bigint
count      integer         count         integer

I am pretty new to MySQL, and i am desired to know that which kinds of table design should be the better one, there pros and cons, and the reasons.

Answer 1

Do you store "blog and comment" in two different tables? If so then the last option where each table have its own votes table is preferred I think. Because blog and comment are two different entities in this case, and votes count is an attribute - the analogous attribute of different entities in one table is not good practice. – https://dba.stackexchange.com/users/150107

I also strongly prefer the second approach. You can add a foreign key to the blog which you can't with the first approach. – https://dba.stackexchange.com/users/10361