Design: Voting or Polling Database Schema
Some time back, I was hit upon a query to prepare a voting database schema. So, here is what I came up with; this is just so it may help anyone looking for a polling/voting schema.
Tracking for an answer, so users are able to vote only once:
Note:
If everyone can vote, registered or unregistered, then we might need to track some other user attributes, for instance:
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.
Happy coding!
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]
Note:
If everyone can vote, registered or unregistered, then we might need to track some other user attributes, for instance:
- Date/time stamp
- IP
- 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.
Happy coding!
