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!