File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes How to avoid duplicate votes Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to avoid duplicate votes" Watch "How to avoid duplicate votes" New topic
Author

How to avoid duplicate votes

Zein Nunna
Ranch Hand

Joined: Mar 31, 2005
Posts: 245
Hi guys,
Sorry about the ambiguous subject, i couldnt phrase it better. Here's my problem..

I will be having a 'voting' system on my website i.e. someone can cast a vote on an Proudtc. Once a person has voted for an item I dont want him/her to be allowed to vote for it again. Hence I need to store this info on my DB i.e. who's voted for what. How do I organise tables for such a thing??
As it stands I have a table called 'User' (userID, name, email..) and a table called 'Product' (prodID).
Users vote for products.
Products has attributes such as name, date, votes (int).

Should I create another table called 'Votes' and have attributes: userID and ProdID and dateVoted, to store which products each user has voted for??

This methodology seems cumbersome, as Products will continuously change, and each user will have the chnace to vote for each product, hence the table will be huge!

Is there a better/more efficient way of doing what I'm trying to explain (badly!)/do??

Thanks in advance,
Zein
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

You could have the database enforce a uniqueness constraint for userId/voteId and while that would prevent the database from storing 2 votes from the same user on the same subject, it's not the easiest to code around. A better solution would be to use the uniqueness constraint (for safety) plus perform the inserts as part of a transaction.

Transaction management for what your describing can also be tricky since:
1) A table lock is extremely expensive
2) A row lock won't prevent new records from being inserted

It might be a good idea to create a row for each user at the time the vote is created this way #2 does not apply and all your statements for voting are updates (initially set to 0).
[ July 07, 2007: Message edited by: Scott Selikoff ]

My Blog: Down Home Country Coding with Scott Selikoff
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
I like the Votes table with userId + prodId to make a unique key. A duplicate vote will get a duplicate key error, which is easy to detect and understand. It also does a good job of recording who did what and when (with the date field) so you'll have the data for unforeseen reporting requirements ... e.g. what products got good votes early and bad votes later?

Define "huge" ... what is the product of users and products? Anything short of many millions I wouldn't worry about as long as you have an industrial strength RDBMS.


A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

The only part I'm not a big fan of is I always thought it was better to avoid SQL exceptions then expect them as part of the normal flow of logic. Granted with concurrency, I'm not sure there's a way around that.
Zein Nunna
Ranch Hand

Joined: Mar 31, 2005
Posts: 245
Thanks guys for your responses.

The way I was looking to implement this was have a Primary key that is prodID and userID.

In my servlet I'll check if the prodID+userID row exists, if it does then i know a record exists which is for a particular product from a particular person hence he shouldn't be allowed to vote, otherwise, if the record doesnt exist I could go on and allow the vote to be counted.

Does this make sense? It simplifies what Scott was saying about have a uniqueness constraint.

Thanks
Zein
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
better to avoid SQL exceptions then expect them as part of the normal flow of logic


A good practice for sure. I'm a little sloppy and pragmatic some times. If one percent of the votes will be an attempt to double vote, would I burden the other 99 percent with a select to see if the row exists before inserting? It's a coin toss for me.
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

Originally posted by Stan James:


A good practice for sure. I'm a little sloppy and pragmatic some times. If one percent of the votes will be an attempt to double vote, would I burden the other 99 percent with a select to see if the row exists before inserting? It's a coin toss for me.


Yea.... that's where it gets tricky... Ideally you'd want some kind of caching mechanism (if usage was high enough) that preventing a user from voting again without ever contacting the database. I try to rely on database functionality at a minimum (especially since a lot of it is non-portable)
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: How to avoid duplicate votes