0

I am going to create an multiple choice based examination system. I am new to database design and want your help to answer a few questions. How to model the questions and answers?

  • Each question will have four options and only one correct answer. option 1:
create a table for questions, options and answer:
question:
  question_text varchar

options:
  question_id int primary_key,
  options_text varchar

question_answer: for storing the correct answer
  question_id int foreign_key,
  answer_id int foreign_key

user_question_answer:  # for storing the response of user.
  user_id int foreign_key,
  question_id int foreign_key,
  answer_id int foreign_key

option 2: store question, options and correct_answer in a single table.

question:
  question_text char,
    option_1 varchar,
    option_2 varchar,
    option_3 varchar,
    option_4 varchar,
    answer enum(1,2,3,4)

user_response:
  user_id int foreign_key,
  question_id int foreign_key,
  answer enum(1,2,3,4)

1 Answer 1

0

Option 2 is a fail - what if people suddenly have 3 or 5 options? REWORK THE WHOLE SOFTAWARE? Also, not relational - the word relation has a meaning there. multiple tables.

2
  • There will always be four option.
    – lorem1213
    Oct 10, 2023 at 19:14
  • Famous last words. Really. That is how the year 2000 problem came around.
    – TomTom
    Oct 10, 2023 at 19:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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