Storing Exam Questions in a Database

Question

I've been thinking about how I should design a database to hold exam questions for a little over a year now (on and off, mostly off).

First, a short description of what I'm after. I would like to design a database flexible enough to store different question types (for example, short response or multiple choice questions), and be able to select any number of those questions to be stored as an exam.

My question is:

How should the exam questions be stored?

Since different question types require different fields to store, if I was to put them all under the same table questions, there will be a lot of extra fields that are never used.

If I separate the question types into different tables, it'll be a lot harder to store the question_id in some exam_questions table, since they will come from different tables.

I also can't think of a flexible way to store the information.

For example,

questions
 - id
 - question
 - type (multiple choice, short response)
 - choice_1 ( used for multiple choice questions)
 - choice_2
 - choice_3
 - choice_4
 - choice_5
 - answer (short response answer here, or just a/b/c/d/e for multiple choice, t/f for true or false)

Would a design like this be recommended? If not, does anyone have any advice?

I also have another question:

If I want to store student responses to one of these exams, would it be inefficient to store them like this?

exam_responses
  - id
  - student_id
  - exam_id
  - question_id or question_number
  - response
  - mark

Thank you in advance. If you would like any other information, or if there is anything wrong with this question, please leave me a comment and I'll try and have it fixed as soon as possible.

Answer 1

I would have separate question and answer tables and join them using a question2answer table

question
--------
 - id
 - question
 - type (multiple choice, short response)

answer
------
 - id
 - GUIorder (so you can change the sort order on the front end)
 - answer

question2answer
---------------
 - questionid
 - answerid

Now everything is dynamic in terms of building the question and you don't have empty columns. A quick join brings you back all the parts to the main question

Your exam_responses table can now have the following

- id
- questionid
- answerid
- studentid
- response
- mark

Answer 2

I think storing five columns for each response in the questions table is not a good design. If the number of choices for the question becomes 6 you will have to add one more column. Also, if there are just two choices, the remaining columns will stay empty. So it better be in two separate tables:

questions
 - id
 - question
 - type (multiple choice, short response)
 - answer (short response answer here, or just a/b/c/d/e for multiple choice, t/f for true or false)

question_choices
- id
- question_id
- choice

Then you can get the list of choices for each particular question by joining them based on questions.id=question_chocies.question_id condition.

In case of exam responses, you should divide in two tables too in order not to repeat information about student_id, exam and mark for each question of the exam. So it should be something like:

student_exam
  - id
  - student_id
  - exam_id
  - mark

student_exam_responses
  - exam_id
  - question_id or question_number
  - response

These two tables can be connected based on the student_exam.id=student_exam_responses.exam_id condition.