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

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

---

Besides these field, I might also need an extra version column to implement optimistic locking.

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

Considering that a comment_vote_counter and a blog_vote_counter are both a specialisation of a more general vote_coutner, makes this a common ORM design issue.

In fact you have the choice:

• The first option is called single table inheritance, because you squeeze several different kind of counters in the same table that provides for all the needed attributes.
• The second option is called a concrete table inheritance, because your concrete classes comment_vote_counter and blog_vote_counter are concrete classes and each has a table with all its attributes.

Both are valid with their advantages and inconveniences. For example:

• Single table is very convenient to use, and would be my fist choice if the attributes of the different kind of votes are very similar.
• Concrete table is very practical for managing subclasses that are very different. However if you frequently work with all the comments together, regardless of their specific type (e.g. if you want to show a chronological history of all the votes related to a post, or search among all kind of votes), then you'd have to use unions of several queries, which is slightly more cumbersome.
• There may also be an issue with the id. A single unique id requires more efforts in a concrete table than in a single table (where you can use DB feature out of the box), if you need it. This also plays a role for associations with another table. For instance, if you want to associate votes with users(one-to-many) or viewers (many-to-many), it's easier done with a unique id and a single table.
• You can also address the relation issue above, with another mapping called class table mapping, which is extremely flexible, but requires a lot of extra joins.

Now you have some more criteria to make your choice: up to you to decide based on your needs.