Search code examples
mysqlsqldatabasevoting

Most efficient database design for this data

My database knowledge is reasonable I would say, im using MySQL (InnoDb) for this and have done some Postgres work as well. Anyway...

  • I have a large amount of Yes or No questions.
  • A large amount of people can contribute to the same poll.
  • A user can choose either option and this will be recorded in the database.
  • User can change their mind later and swap choices which will require an update to the data stored.

My current plan for storing this data:

  • POLLID, USERID, DECISION, TIMESTAMP

Obviously user data is in another table.

To add their choice, I would have to query to see if they have voted before and insert, otherwise, update. If I want to see the poll results I would need to go iterate through all decisions (albeit indexed portions) every time someone wants to see the poll.

My questions are

  1. Is there any more efficient way to store/query this?
  2. Would I have an index on POLLID, or POLLID & USERID (maybe just a unique constraint)? Or other?
  3. Additional side question: Why dont I have an option to choose HASH vs BTREE indexes on my tables like i would in Postgres?
Solution

  • The design sounds good, a few ideas:

    A table for polls: poll id, question.

    A table for choices: choice id, text.

    A table to link polls to choices: poll id->choice ids.

    A table for users: user details, user ids.

    A votes table: (user id, poll id), choice id, time stamp. (brackets are a unique pair)

    Inserting/updating for a single user will work fine, as you can just check if an entry exists for the user id and the poll id.

    You can view the results much easier than iterating through by using COUNT.

    e.g.: SELECT COUNT(*) FROM votes WHERE pollid = id AND decision = choiceid

    That would tell you how many people voted for "choiceid" in the poll "pollid".

    Late Edit:

    This is a way of inserting if it doesn't exist and updating if it does:

    IF EXISTS (SELECT * FROM TableName WHERE UserId='Uid' AND PollId = 'pollid')
    UPDATE TableName SET (set values here) WHERE UserId='Uid' AND PollId = 'pollid'
ELSE   
    INSERT INTO TableName VALUES (insert values here)