5

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?

CJ Dennis
  • 4,226
  • 2
  • 40
  • 69
Sagiruddin Mondal
  • 5,407
  • 2
  • 29
  • 44

2 Answers2

13

I think, 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 defauld 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, I would like to mention that, 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.

The Alpha
  • 143,660
  • 29
  • 287
  • 307
  • 1
    Awesome, Sir. Thank you for this beautiful Answer. – Sagiruddin Mondal Nov 05 '13 at 22:16
  • 3
    although you could to one better - use an unsigned tinyint with a downvote being -1 a vote being 1, then you can get the total score easily with `` `total = SUM(vote)` `` – Hailwood Nov 06 '13 at 03:42
  • how to do the same for relation in post? for ie i want to fetch count of upvotes and downvotes as a relationship ,is there any way to do it? – Jatin Parmar Jun 19 '21 at 11:56
3

I would prefer to only have one table containing the votes, this could be done with an extra column such as is_downvote int(1).

It seems that you havn't tried much which is always a negative. For this scenario the Laravel Eloquent Documentation should be plenty to figure this out.

I would of written this as a comment but it's pretty lengthy now.

SamV
  • 7,548
  • 4
  • 39
  • 50
  • 1
    Thank you for your kind reply. Its true I have not started the hard coding about it. The the only reason is as it is going to handle a big size of data , so I need to be sure about the schema. As you have told I can do it with one voting table, But I am not sure about the management of two different types of vote there ... do you know about any existing schema design of it ? – Sagiruddin Mondal Nov 05 '13 at 11:34
  • 1
    It was just an opinion based off this being used for small - medium applications. I don't have the knowledge to provide an accurate answer; just that there's advantages and disadvantages to both approaches. – SamV Nov 05 '13 at 11:47