Breaking News

Saturday, July 31, 2010

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
    varchar body
    datetime created_at
    datetime updated_at

Table: poll_answers
    int id
    int vote_id default 0 (foreign key to
    int question_id (foreign key to
    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
    int answer_id (foreign key to
    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. 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!!!

  2. Here is great blog on parental control systems. Read it if you considering to use something like that.


Designed By Published.. Blogger Templates