Up vote and down vote in Laravel eloquent

I want to create an up vote and down vote system for my website where a unique user can vote up/down for one post and next time he only allow to opposite to get off from database and after that he again can up or down vote.

In this case I have:

users table :

id 
name

debates table :

id
post

upvotes table:

id
user_id
debate_id

and similarly downvote table:

id
user_id
debate_id

Is that a good way to manage and track up vote and down vote concept?

Solution

You can use a single table to track the votes and the structure could be something like this

Table : votes

id | user_id | debate_id | vote

Here, vote field could be tinyInt with default null.

And, in vote field, you just keep two different values depending on the vote type, for example, if a user up votes then insert a value of 1 in the vote field and for down vote, insert the value of 0. So, your table may look something like this

id | user_id | debate_id| vote
1  | 10      | 4        | 1 <-- up
2  | 11      | 4        | 0 <-- down
3  | 12      | 4        | 1 <-- up

In this case, two users with id = 10 and id = 12 up voted the post whose debate_id = 4 and another user with user_id = 11 down voted on the same post. In this case, you may find out how many up or down votes a post got by counting the vote field's value, for example, you may count for up votes for debate_id = 4 using something like this

$count = Debate::where('debate_id', '=', 4)->where('vote', '=', 1)->count();

Also, you may use something Query Scope, this is just an idea and it's not possible to make an answer which covers everything in this scope. You should start using some basic idea and if you stuck at a certain point, then you may ask specific questions with your code.

Also, if you find a user id in the votes table with a certain debate_id then this user has voted on this post and to find out the vote type, just check the vote field 1 or 0.