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

3 comments:

  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!!!
    :)Best

    ReplyDelete
  2. Anonymous6:38 AM

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

    ReplyDelete
  3. People have to go and stand in huge lines just to cast a single vote. Some even believe that their vote doesn't count because of unfair election results.https://sweepstakestips.cabanova.com/

    ReplyDelete

Designed By Published.. Blogger Templates