2

I'm working on a smallish voting webapp. Similar to strawpoll but only authenticated users are allowed to make polls, while everyone is allowed to vote.

User:

  • id
  • email/password_hash/name/...some other data...

Poll:

  • id
  • user_id REFERENCES User.id - notes which user created the poll
  • title

PollOptions:

  • id
  • text
  • poll_id REFERENCES Poll.id - notes which poll the options belongs to
  • count - Number of votes for this option

Votes:

  • id(optional?)
  • poll_id REFERENCES Poll.id - notes which poll was voted on
  • user_id REFERENCES User.id - notes the user which voted
  • ip_address - If the the person who voted isn't registered, ip_address will be filled to prevent him from voting multiple times.

I want the owner of the poll to be able to delete polls and change the poll (add more options, remove options, ... ) Is my design good enough? If not, what should I change?

2
  • Entity names should be singular, POLL_OPTION, not POLL_OPTIONS.
  • VOTE should reference POLL_OPTION, not POLL
  • FK from VOTE to USER must be not mandatory (allow null) to allow non-registered users to vote.
  • COUNT is a calculated field, it's either not necessarry or it has to be updated post-poll by counting rows in VOTE for every option.
  • Take in mind that when you delete a POLL you must do it in cascade or the deletion will fail if polls already have options, and options already have votes.
  • PK of POLL_OPTION should be (POLL_OPTION_ID,POLL_ID), that way FK from VOTE to POLL_OPTION will have to be also (POLL_OPTION_ID,POLL_ID). That will allow you to make (USER_ID, POLL_ID) the PK of VOTE. That way the same person will not be able to vote twice in the same POLL. If you are into surrogates, then add VOTE_ID, make it the PK but also add an unique index on (USER_ID, POLL_ID) to achieve the same constraint. enter image description here

UPDATE:

Since OP stated that keeping who voted for what option to be kept secret, here's another solution:

  • VOTE has PK to POLL as OP's original design. Only a vote per person is allowed via PK and or unique index on EMAIL_ADDR.
  • But another entity called BALLOT, not related with USER, will hold a row for each vote. The results of the poll will be counted from the rows on this table. If you multiple users were to update the COUNT in the same row at the same time you will have blocking problems.
  • After the poll is finished you can calculate totals by counting the ballots and update the COUNT on POLL_OPTIONS. Then you can either delete the ballots or keep them for audit.

enter image description here

  • Hmm, I guess it wasn't clear from my post, but I don't care what particular option the user voted for, just that he voted in that particular poll once. But yea, your solution is a lot better if I allow the user to change what he voted for or something. Thanks for suggestions. – Marko Stojanovic Aug 19 '16 at 17:21
  • @MarkoStojanovic You mean that you want to keep the vote secret? – Tulains Córdova Aug 19 '16 at 17:26
  • @MarkoStojanovic in that case I'll change my answer. – Tulains Córdova Aug 19 '16 at 17:34
  • @MarkoStojanovic A secret vote alternative was added. – Tulains Córdova Aug 19 '16 at 17:50
  • It's not so much about keeping it a secret, it's more that I planned to use VOTE only to check whether the user voted in that particular poll or not. But since you pointed out that I might have blocking problems with count, I'll probably use the solution from your original answer, and aggregate count from POLL_OPTION. – Marko Stojanovic Aug 19 '16 at 19:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.