Donations VS Votes

Hi, my site allows donators (members) to make donations to charity competitions. The donators can also vote in a poll for each charity competition.

When a user makes a donation to a charity competition, they also have the right to submit one vote.

QUESTION
I want to change the system, so that the donators can make as many donations as they like to a charity competition but still only have one vote.

My current database looks like this:

id
charity_comp_id
member_id
donation_amount
vote_status (submitted / not submitted)
vote_answer

How should i setting this up so that users can submit as many donation as they like per competition but they are still only limited to one vote? Thanks in advance for your help…

Two tables, one for the donations and one for the votes.

Ok cool, then just check to see if a record exists in the ‘vote’ table where the member_id and charity_comp_id and votes status exitst

You could probably just make member_id and charity_comp_id unique by enforcing a UNIQUE index on that table, any attempt to add another row then throws a particular mysql error message upon whose appearance you can fork your code, generate a warning or log it etc.

That’ll save the extra query to see if a record exists every time you get a bone fide vote (ie the vast majority of the time, no?)

You could also do an INSERT IGNORE if you’re not concerned about showing an error to the user or logging it.

Yeah, nice one.

As far as votes go, at least in this situation, I would probably go with the insert ignore option. What would be the point of showing an error? If they’ve already voted on that one, just let them move on. I wouldn’t want to stop the flow of what they’re doing (for usability reasons) just to tell them something as simple as “You’ve already voted for this”. I’m sure they won’t care enough about that to justify the interruption in their workflow.

As far as resources go… there wouldn’t be much difference in performance. You’re hitting the DB either way. Either to check if the vote is already there, or to perform an insert that never actually happens.

Another option would be to check if they’ve voted for that charity already when the user loads the page. Then you could grey-out/disable the voting option so they can’t vote. Then they’d already know that they’ve voted on it before they try to vote again. You can combine that with the insert ignore option on the back end to prevent them from manually going to vote.php?charity_id=1 (or whatever your URL scheme is) and voting again when you’ve disabled the vote link.

Excellent, thanks for your detailed input into this…