6

I'm designing a database for a polling app. Users get to answer a bunch of questions and we store them.

The relevant ERD part enter image description here

I want to store the correct answer for each question so we can check whether they answered the question right or not.

The questions can have different "types":

  • string
  • number
  • single_response
  • multiple_response

How should I store the correct answer?

Adding correct_answer to both questions and options

adding just a correct_answer field to questions wouldn't work in case the question has type multiple_reponse. I've thought about adding correct_answer to both questions and options where questions would have a string or number with the correct answer and options would simply get a true or false. We'd check if all options with correct_answer set to true are selected or not and if an option with correct_answer set to false is selected

Creating a new table called correct_answer

Add 2 columns:

  • correct_answer
  • options_id

And then check if either is set. We'd have to create a new correct_answer for each option.

Adding correct_answer to questions

We could use type to determine how to read correct_answer and then either check correct_answer directly or explode() a comma delimited option_id value to check the answer

I'd like to know which option is the best practice or if anyone has a better idea.

My entire sql: fiddle

1

How about adding 2 Columns in Question_Option entity, one as IsCorrectAnswer and HasMultiAnswers ? You can letter check the counts of answers where HasMultiAnswers is set to true.

  • Good idea, I'll see if it works for me :D, thanks! – g3mini Nov 17 '16 at 14:30

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.