Storing the correct answer to check against 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


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