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_answeroptions_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
Best Answer
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.