Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

dmytroshved's avatar

boolean column VS smallInteger column for storing likes/dislikes in MySQL

Hello

I have a question about 2 approaches of storing likes & dislikes in MySQL db

  1. boolean

In this approach we will use true for likes and false for dislikes, if user wanna to remove his vote (unlike or undislike) we will delete record:

        Schema::create('likes', function (Blueprint $table) {
            $table->id();
            $table->foreignId('user_id')->constrained()->cascadeOnDelete();
            $table->foreignId('recipe_id')->constrained()->cascadeOnDelete();
            $table->boolean('liked'); // 1 => like | 0 => dislike
            $table->timestamps();

            $table->unique(['user_id', 'recipe_id']);
        });
  1. smallInteger

We will save the vote value in the vote field, where 1 will mean liked and -1 disliked. We will also set the value to 0 if a user removes any of the options.

        Schema::create('votes', function (Blueprint $table) {
            $table->id();
            $table->foreignId('user_id')->constrained()->cascadeOnDelete();
            $table->foreignId('recipe_id')->constrained()->cascadeOnDelete();
            $table->smallInteger('vote');
            $table->timestamps();
        });

"It always depends", but I would like to get your feedback

Would be grateful for your opinion

Best regards

0 likes
7 replies
JussiMannisto's avatar

I'd use tiny integer with -1/1.

  1. You can run sum(vote) to get the total.
  2. I think it's conceptually clearer. The meaning of liked = 0 is not as obvious.
1 like
Snapey's avatar

Like is a boolean, You either like it or you don't (or you haven't expressed an opinion. It makes sense as a boolean field and the number of likes can be simply counted. This is the same concept as votes.

This is different to the concept or Approve or Disapprove. A disapproval might cancel an approval. THEN it would make sense to use 1 for approve, -1 for disapprove or 0 for no opinion.

In your case, simple boolean is the way most would do it, however your application may be more nuanced, so its your choice.

1 like
dmytroshved's avatar

@martinbean Its not the same topic, in my previous post I was asking about:

"Should I create two separate pivot tables: recipe_likes & recipe_dislikes OR recipe_socials (with likes and dislikes inside)?"

but here I am asking about difference in two approaches to save likes/dislikes in ONE table

Best regards

martinbean's avatar

but here I am asking about difference in two approaches to save likes/dislikes in ONE table

@Dmytro_Shved …and I gave advice to that very question in your other thread.

2 likes
dmytroshved's avatar

@martinbean Yes, youre right. I was using true/false logic before creating this post and didn't see your message:

"You should use a tiny integer column, and then 1 for likes or -1 for dislikes. That way, you can get the overall sentiment by summing the values."

my bad

best regards and happy coding!

Please or to participate in this conversation.