Feature Post


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.

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
  • 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!

1 comment:

  1. Hello, just one notice about "Tracking for an answer, so users are able to vote only once:" and table 'poll_voting_history'.
    To ensure that with this table state, an unique index key must be on three foreign keys and PK int AI at once(No duplicate votes: {id1,Q1,A1,U1} and again {id2,Q1,A1,U1}.

    The solution is composite PK (three foreign keys) so combination is UNIQUE by default => only one vote per person per referendum.

    Keep the data integrity!!!