Storing Exam Questions in a Database
database, database-design, database-schema, sql
Solution
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
Problem
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.