CODEWITHSUNDEEP
phpmysql
Serg
Serg

Reputation: 606

Vote system with IP history

I'm bulding app with ability to vote (+1/-1) and trying to have simple and fast mysql tables structure for this. So far my idea is straightforward and was to have something like:

tbl_votes Stores votes value for certain object:

CREATE TABLE IF NOT EXISTS `tbl_votes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vote` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

tbl_votes_ips Stores IPs<=>vote history so certain IP can vote only once:

CREATE TABLE IF NOT EXISTS `tbl_votes_ips` (
  `vote` int(11) NOT NULL,
  `ip` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

The aim is to reduce SQL calls when inserting new values. The purpose is to use on highly loaded website. What is the best practice for such functions?

Please share your experience.

P.S. I have the idea of making unique key for vote-ip pair, so I don't select and rather just handle mysql "not unique value" error:

ALTER TABLE `tbl_votes_ips` ADD UNIQUE (
`vote` ,
`ip`
);

But that's won't work for a case when user is changing it's choise. So anyway I need to make extra call to search for IP and vote.

Upvotes: 0

Views: 473

Answers (3)

Eric J.
Eric J.

Reputation: 150138

Alternative A

Require user registration and login.

Alternative B

  • Set a unique cookie. If it is present on later visits, use it.
  • Concatenate the browser user agent + IP address and create a hash of that. Use that hash on future visits, if the cookie is absent, as a proxy for whether that user has voted. It will be about 85% reliable.
  • If you wish, you can include more factors beyond user agent + IP, but that is more involved. For more details see Panopticlick.

Alternative C

Use Evercookie. If you go that route, be exceedingly clear in your privacy policy that you are doing this. Companies have been sued (some successfully, some unsuccessfully, but either way costing them time and money) by using but not disclosing use of these techniques.

Limitations

Keep in mind that any of these techniques are not that hard for someone to work around. Multiple user registrations are just a free email account away. Many home internet connections get a new IP if you recycle the cable box. Smart phones get a new IP by driving down the street a mile or so. Even Panopticlick fails if the user has multiple browsers on their system.

Upvotes: 2

GGio
GGio

Reputation: 7653

How about have vote per user? Give the user the ability to register using email and make sure you send the confirmation email to their email so that they verify it. And then you got your unique identifier to disallow same user for voting an item more than once regardless where they log in from.

If you want set of users to be disallowed from voting a certain part then you need to create an user class and relate to the users table so that John and Smith will have same user class and be allowed to vote only once.

Upvotes: 0

Marc B
Marc B

Reputation: 360762

Use a unique key, e.g.

PRIMARY KEY (vote, ip)

then a simple

INSERT INTO tbl_votes_ips (vote, ip) VALUES ($vote, $ip) 
ON DUPLICATE KEY UPDATE SET vote=VALUES(vote)

which can be done as a single query and mysql will handle deciding if it should be an insert or an update.

And, as Eric J noted above, your system is going to fall on its face for any group of users sharing a common proxy/nat gateway. And also will not handle users on IPv6. While it's not particularly widespread yet, it's getting to the point that IPv6 support cannot be ignored anymore.

Upvotes: 0

Related Questions