Not sure if this question belongs to this site or some other one (please point me to the right one if this is not it).
I am trying to design the Quiz module for an LMS type of system. This question is related to database design.
My Current design:
Database
1) Questions : multiple choice
[id, question_text]
2) Answers : Possible options for the above questions [question_id, answer_text, correct?]
3) Quizzes : Quiz composes of questions. They are also related to various topics. Certain quizzes are like Reading comprehensions (with one paragraph and then a set of questions), others are just simple quizzes with questions [id, questions, description, topic]
4) Topics : The topics to which a quiz belongs to. Currently each quiz belongs to one topic only. [id, topic_name]
5) Quiz Responses : They store the quiz_id, question responses, received marks and time taken. [id, quiz_id, user_id, correct, time_taken]
App logic
1) Quiz, question and answer are generic constructs that store information
2) To display a quiz, we get the question set, see its associated tag and run a display logic based on the tag (All quizzes associated with reading comprehension etc display the quiz description, which has the paragraph, and then the questions; Other quizzes just show the question and answers)
Question
1) Is this the right approach to design the application? Or are there better approaches I could think of (Dividing the quiz into sub-tables of various types etc)
2) I want to add bookmarking to the site. Users bookmark questions and then can see these questions on single screen. The problem here is that questions related to RC quizzes should fetch the quiz paragraph for display also. What is the best approach to take in this case?
The options I could think of
Putting the paragraph details into a separate table and storing its id in both quiz and associated questions. The problem here is wasted space (since only a small percentage of questions are RC).
Re-creating the above set of tables (Quiz, Question) for RC specifically. Lots of duplication :/
Anyone who has experience with this topic or has an idea of how orgs like moodle handle these situations?
I think you want something like this:
Note that QUESTION
s are organized into QUESTION_GROUP
s. For reading comprehension questions the question group could record the details of the reading to which the questions applied. For some quizzes, there may be only one question group, for other quizzes there could be many.