0

So I designed database relationships for a voting/polling application. I want a user to only be able to pick 1 option on a poll. I want multiple options per poll.

enter image description here

So what I have here is this.

User

  • has an id, username, email, password
  • the primary key is its id

Poll

  • has a title and an id
  • the primary key is its id

Option

  • has a text, and an id and holds a reference to the id of the poll that it belongs to
  • its primary key is its id PLUS the id of its parent poll

Vote

  • holds a reference to the id of the user that voted
  • holds a references to the selected option

So how does this look? I have set up these tables and I can insert records for polls, options and users but when I want to add a vote the options I get are not what I expected. I want to add a vote per poll where I can pick exactly 1 option.

This is my first time designing a database, any help would be great.

  • It's unclear to me how your joins between the Options table and the Votes table work. Perhaps you're trying to enforce one option per poll with a join? I think you could probably do that with a simple database constraint instead. – Robert Harvey Apr 14 at 23:04
  • I want multiple options per poll, but I want only 1 'selected option' per user. What structure do you propose? – Alexander Ameye Apr 14 at 23:09
  • If you make your join between those two tables one-to-one, it will automatically restrict your user's choice to a single option anyway. – Robert Harvey Apr 14 at 23:15
  • Your specific restrictions suggest that you need a user_id field in your Options table. – Robert Harvey Apr 14 at 23:16
  • If the constraint must be explicit as relationship. Are not you missing poll_id in votes table? All three ids make a unique key constraint. – Laiv Apr 15 at 6:11

Your Answer

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

Browse other questions tagged or ask your own question.