CODEWITHSUNDEEP
mysqldatabaseperformance
yeahman
yeahman

Reputation: 2767

Generic votes table vs separate votes tables?

I want to implement a vote system for several different entities/tables (e.g. articles, blog posts, users).

What is the best/more efficient approach?:

  1. Create a table votes to store all the votes of all entities?

    votes

    • vote_id
    • user_id
    • type (articles, blogposts or users)

  2. Create a table votes for each entity? votes_articles, votes_blogposts, votes_users

What I see is: First option will result with a bigger table and there's an additional field which I need to include in my queries. More generic table that can be easily extended for more entities if needed and everything is kind of centralised. (Can use a generic function to retrieve/insert/update the table.)

Second option will result with smaller tables; faster to query? But not necessarily better to maintain.

Upvotes: 1

Views: 130

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270443

The second method has many advantages. Presumably, the votes are actually on entities, so you also have an id in each table pointing to the article, blogpost, or whatever that is being voted on. In a standard SQL database, you would like to have foreign key references to other tables, and the one-table-per-entity approach provides that capability.

You could modify the first approach to do this. However, that would require a separate column for each possible entity. And, then, you lose the easy flexibility of adding new entities.

When is the first approach advantageous? First, when maintaining valid foreign key references is not important. And, when you often want to bring together votes as votes. So, how many times did a user vote today regardless of what s/he voted on? How many votes do user A and user B have in common regardless of what they voted on? Get the idea. If votes starts to behave like its own entity, then it deserves its own table.

I happen to think that your very question highlights a major weakness in SQL and relational databases. This is an example of wanting different entities to "inherit" features from a class (to borrow terminology from the OO world). Wouldn't it be nice if you could just specify that a new entity inherits properties from another entity (such as "Votable")? Oh, never mind, that's not the real world of popular databases. At least not today.

EDIT:

If you care about performance, don't go with the modified first approach -- that is, a separate column for each possible entity. Normally, primary keys are 4-byte integers. These (in most databases at least) will occupy four bytes, regardless of whether the column has a NULL value. So, one table with three entity columns is (to a very rough approximation) three times the size of three tables specialized for each entity. Such wasted space only slows down the query processing.

If you are only going to have two or three entities, maybe this isn't that big a deal. But once you get to more than you can count on one hand, it really is a waste of space, memory, and processing power.

Upvotes: 3

Related Questions