You are currently browsing the tag archive for the ‘Poll’ tag.

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.

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!

Advertisements
%d bloggers like this: