Table: poll_referendum int id varchar name Table: poll_questions int id int referendum_id (foreign key to poll_referendum.id) varchar body datetime created_at datetime updated_at Table: poll_answers int id int vote_id default 0 (foreign key to vote_types.id) int question_id (foreign key to poll_questions.id) datetime created_at datetime updated_at
Tracking for an answer, so users are able to vote only once:
Table: poll_voting_history int id int question_id (foreign key to poll_questions.id) int answer_id (foreign key to poll_answers.id) int user_id (foreign key to the id in the users table) datetime created_at datetime updated_at Lookup: vote_types int id varchar type [yes | no | abstain]
If everyone can vote, registered or unregistered, then we might need to track some other user attributes, for instance:
- Date/time stamp
- User agent
- User cookie attributes
- Besides Referendum_id, question_id, answer_id, vote_id, etc
But even this is easily "tamperable" if user changes any of the above fields, or use a Tor-like software. In that case we would need to look into some pattern recognition algorithms or research some other techniques to track user.
Following article might help you get a jump start.