2

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.

8
  • The pros and cons only matter if they matter. I mean, it depends on your needs. A pro for you can be a con for me. The only thing that matters if solves the problem. That said, source code and mappings aside, which design do you think is easier to query and maintain? For example, where are we supposed to go to check all the types available in #1? How Type affects the queries we need in the application? how many types do you expect to have? It's obvious to me that #1 comes with a cognitive burden #2 doesn't have. The type. – Laiv Jun 19 '20 at 9:13
  • @Laiv I use spring jpa(Hibernate) to do the ORM job, about the source code and mappings aside, both seems not complicated right now. What about the performance problem here? – user8510613 Jun 19 '20 at 9:15
  • Have you any performance issue right now? Did you take any metric? Are you trying to solve a problem you don't have yet? Maybe you think 1 table will make a big deal improvement better the performance, but you are missing that it's likely you will have to add a 3rd table TYPES and do join with this table every time you query VOTE_COUNTER. So not a big improvement. So the question boils down to "what option is the simplest one"? – Laiv Jun 19 '20 at 9:17
  • Why the one table strategy can make performance improvement – user8510613 Jun 19 '20 at 9:21
  • It won't. That's the point. I'm sure there's no 1 table design vs 2 tables design. There will be 2 tables all the time – Laiv Jun 19 '20 at 9:22
5

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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