Set up voting system for videos

[defroster]

Hello,

 

I am planning on adding a voting system for videos on my website. Setting up the database. Which is better?

 

1. To have the votes included in the same table as the video information

 

Table videos
-------------------------
id (int)
name (varchar)
upvotes (int)
downvotes(int)

 

2. Have a separate table called 'votes'  in connection with the videos table that have the following colums, that looks like this.

Table votes
---------------
id (int)
video_id (int)
upvote (int)
downvote(int)

 

 

- If #2 is the best solution, would you use each vote to be one row in the database, or just to update the 'total vote count' for each corresponding video? i.e.

id/video_id/upvote/downvote
---------------------------------------
01/055/1333/100
(Example: 1333 votes up and 100 votes down)

 

 

Thanks so much for help /df

[Philip]

Let's dig a little deeper:

 

- Do you have users

--- if yes, do you want to be able force them to only vote once, or to track their average ratings?

--- if no, will/do you want to in the future?

[defroster]

Thanks for reply, yes they should only be allowed to vote once per video.

[Philip]

Then I would do:

 

Table votes
---------------
video_id (int)
user_id (int)
vote (bool)

 

vote - 1/true for upvote, 0/false for downvote

[defroster]

Thanks a million. That is what I'll try doing then. /df

[defroster]

When I am trying to set up the 'vote' table in phpmyadmin. I select BOOL, but then when after it is created it says TINYINT(1) ??

 

Is this normal, or am I doing something wrong? Thanks

[Philip]

That's normal. There isn't a true boolean (in sense of true/false) in MySQL, but tinyint(1) is the same thing as 1/0 for true/false

[defroster]

Thanks. You mention earlier you would use negative one (-1) for a vote up and 0 for a vote down. Is there any advantage using a negative one (-1) as opposed in using a positive one (1)?

 

Thanks /df

[defroster]

If anyone knows if there is a difference in using 1 or -1 would be very much appreciated thanks

[Philip]

It was supposed to be a +1. The dash was just separating my text, sorry for the confusion.

[defroster]

OK, great. Thanks. Positive 1 it is, that makes more sense. /df