database-design: table of likes?

Question

I wonder,
at many websites there is the option to like/dislike a post.
even here of course, at stackoverflow.

so, technically it's a big likes table?

user_id    post_id

user_id - who voted
post_id - which post is it

is that all about?
a big likes table?
isn't there something more efficient/sophisticated?

Solution 1

At it's most basic level, yes, that's all it is.

But then it starts to expand, trying to answer questions like:

• How much did the user like this post?
• When did they like the post?
• How did they find the post?
• Did they comment on the post?
• How does the entire group/community like the post

Then you start to want to answer more in depth questions about friends and the community.

Solution 2

I think I can understand your concern. Having to issue a COUNT() everytime the page needs to be presented.

You certainly have to have this basic table, which will be the basis for a COUNT(), but you don't really need to COUNT() it for every access.

Create a totalling table and update it either when the page receives a like or dislike, using a trigger, or call a stored procedure to update it from time to time.

I would say each method is more indicated for different site personalities, ie, more readings or more writings, but you already know that when it comes to likes or dislikes nothing is previsible.

Comments

[Majid Fouladpour]

On what basis do you assume that is a big table?

[JJJ]

If you need to retain the information about who has liked/disliked a post, that is the minimum amout of data you need to store.

[socksocket]

so practically, suppose I got a Post table which can be liked. it should have a field "likes_counter" which count the number of likes so I don't have to use COUNT(). right?

[Gustavo Pinsard]

Correct. Using your target table, adding a column to it is an approach that saves an extra table in the server. If you're allowed to change the structure of the target table, great. If not, create a separate table which will contain two columns: targetTableName, totalLikes. I suppose you have full access to the db. My considerations were merelly to call attention to the different design alternatives.