• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
What should my type be for votes in db

#1
I have my votes column in my database and have set it at 

name = votes
type = tinyint(3)

When the user clicks on button it adds another vote to current votes

But not sure if set type correct. Because if is a negative number -15 example not sure if tinyint(3) can handle it.

Thanks

[Image: 394I5j2daOwQ.png]
There's only one rule - please don't tell anyone to go and read the manual.  Sometimes the manual just SUCKS!
Reply

#2
A tinyint datatype can only store 1 byte of data, storing a max value of just 255. So you cant store a number higher that 255. I guess thats not desired for a voting system to have such a limitation.

http://dev.mysql.com/doc/refman/5.7/en/i...types.html

I would store all votes as unique entries in a different table with more data then you really need but can prove valuable when your voting system gets hijacks or something. Atleast then you have the ability to remove some less desired votes after you have investigated an issue. (for example, when you get 10.000 of votes in an hour originating from some country far far away)

votes:
id, ip, timestamp, useragent, referer, votes_for, etc

Then when you want to display the total number of for for just let the database handle that for you.
Code:
SELECT count(votes_for) as total_votes FROM votes WHERE votes_for='xxx'
Reply

#3
(01-16-2017, 02:11 AM)Diederik Wrote: A tinyint datatype can only store 1 byte of data, storing a max value of just 255. So you cant store a number higher that 255. I guess thats not desired for a voting system to have such a limitation.

http://dev.mysql.com/doc/refman/5.7/en/i...types.html

I would store all votes as unique entries in a different table with more data then you really need but can prove valuable when your voting system gets hijacks or something. Atleast then you have the ability to remove some less desired votes after you have investigated an issue. (for example, when you get 10.000 of votes in an hour originating from some country far far away)

votes:
id, ip, timestamp, useragent, referer, votes_for, etc

Then when you want to display the total number of for for just let the database handle that for you.
Code:
SELECT count(votes_for) as total_votes FROM votes WHERE votes_for='xxx'

Do you think

Code:
BIGINT

Will do or INT
There's only one rule - please don't tell anyone to go and read the manual.  Sometimes the manual just SUCKS!
Reply

#4
The whole reason I gave you the link is so you can have a look at it... The documentation is very clear on what maximum value you can store in each datatype. You will have to figure out what you think is reasonable to pick as a datatype to be on the safe side of possibilities. Just don't put in a bigint just because it can store the highest number. Having a to large data type for your usage will lead to a less effective database, it uses up more dead space and will cause slightly slower queries.
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.