Multiple rating system for posts (like, love, star)

Question

I need to develop a voting system for posts. Every user can like a post by clicking on thumb icon and love a post by clicking on hearth icon, in the future maybe I have a star system as alternatives.

I'm looking for better solutions to implement this system, for now, I've thought this two way:

• 1) one table for each voting system

tbl like

id
post_id
user_id
value // value even 1

tbl love

id
post_id
user_id
value // value even 1

tbl star

id
post_id
user_id
value // value between 1 and 5

• 2) one single table for all voting system

tbl rate

id
post_id
user_id
type // 'like' or 'love' or 'star'
value // value between 1 and 5

Which is the better solution?

And other thing, when I query multiple posts isn't good SELECT and sum (or avg) every vote, maybe is good to save in posts table the sum (or avg) of current like/love/star to speed up the query, and then update the posts on every new like/love/star?

The last thing, to let the user see if he has already voted some posts, it's a good solution a query like this? SELECT post_id FROM rate WHERE user_id = <MYUSERID> AND post_id IN (<ARRAYOFPOSTS>) and then compare the retrieved post_id with the post_id of the main query?

Thank you!

Answer 1

As Eric mentioned, having multiple tables is not usually a very good idea performance wise.

For each table MySQL is required to maintain internal data structures, a data dictionary, file descriptions and so on...

Go with solution 2.

This would be the best solution in terms of both performance and scalability of your application for the future. Instead of having to support a completely new table when expanding votes, you simply need to allow your application to show a new type of vote in the frontend.

When fetching statistics for the given article, you could as you say, save this every time a user votes to not have to fetch the full count each time, however the performance gain won't really be noticeable at all, instead make some good indexes to speed things up once it's needed.

If you wish to show the user if he has voted for a given post I would recommend simply checking the rate table you describe in solution 2. If you wish to limit amount of queries from your application you could as you say fetch all posts on the page, then query on those. However the performance gain here is also minimal I would say.

Some good indexes would solve most of your performance concerns for the foreseeable future. Usually the only time you'll need to split the data is when the whole database can't handle all the traffic. This article from the Airbnb team gives an idea of when that might be the case.

A small tip would be to not overthink the application. Usually it's better to start out small and do things the easy way. This way you won't introduce more issues than necessary. Once the application then grows, the issues will come naturally and you'll solve them as they come.

Answer 2

Your second approach is a "simple" implementation of the Entity–attribute–value model. But there is nothing really simple about EAV in practice. Usually EAV is used for user defined attributes. This is not the case here. And I don't see any other good reason in your post. "I don't want to create a new table or column every time I implement a new feature" is a bad one.

There is also no general rule, if one table is better than multiple tables. The pure number of tables says nothing about the quality of a database design. You need to analyse the requirements, the entities and relations. And that brings me to the next point..

Your first two tables are not even entities. "User likes a post" and "User loves a post" are pure relations. The value column makes no sense, since it can only contain 1, and thus holds no information. So your tables should actually look like:

user_post_likes (
    user_id (FK, PK)
    post_id (FK, PK)
)

user_post_loves (
    user_id (FK, PK)
    post_id (FK, PK)
)

user_post_ratings (
    user_id (FK, PK)
    post_id (FK, PK)
    rating [1-5]
)

The first two tables are clearly different from the third one. Merging them into one table would be a strange idea. And the first two tables should neither be merged, if they are actually two different relations, even when they have the same signature. (It would be similar to merging two functions sum(x,y) and diff(x,y) into calc(operator, x, y).)

The next point depends on the requirements. I doubt that you ever want a post to be liked and loved at the same time from the same user. If likes and loves are exclusive, then it should be one entity. The table could be something like

user_post_flags (
    user_id (FK, PK)
    post_id (FK, PK)
    flag [like|love]
)

(Sorry - finding good identifiers is a hard task ;-))

You can code 'like' and 'love' with 1 and 2 (or whatever you want). And while it now looks like the ratings table, they are still two different things, and should have separate tables.

Regarding performance: This highly depends on the task/requirement. But I never heard of EAV model being chosen for performance reasons. (Except of myself, but that doesn't count :-).)