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...
Table Donation id charity_comp_id member_id donation_amount Table Vote id member_id vote_answer Set member_id and vote_answer together as unique. that means that the database will not allow multiple votes from the same member.
Separate the donation and vote tables. You can then add a unique composite index to the vote table on charity_comp_id, member_id. This will maintain integrity in that you cannot add more than 1 vote per member per event to the table.
Ok cool, thanks guys, that makes perfect sense... so basically, check to see of the donator has a record in the vote table for that charity_comp_if WHERE status = submitted