Creating an exam center database

Question

I am making an app than has an exam center... I am aware of the database normalization concepts...but in my case it looks like I need to store non atomic values in a column. Actually the app will have 2 screens -

1. Exam packs (that contains a set of questions) e.g. Kids Science, Modern Physics, History etc.

2. Custom Exams...where one can pick subjects and then conduct an exam.

So... ofcourse there should be a table called questions with

Now I have to fetch some out of these questions if user selects an Exam pack or set of subjects. It was clean till the Exam Pack was into picture. Now I have two options:

1. Create a columns called questions in exam_packs table that will contain the comma separated ques_ids or an array.

2. Create separate tables for each exam_pack with questions in them... by doing so..these questions could not be available for users taking exam by subject.

Any suggestions are welcome.

Answer 1

To explore a bit more about the question and comment, I want to note that generally when normalizing your goal (among others) is to reduce duplication.

A naive example of your table might be everything you have but adding things like the CSV list you mention, this ends with a very wide table that has multiple areas of duplication and which will require you to manage and clean your data (and is generally bad for performance.)

A solution to this problem is to simply project the data into another table and provide the keys to join back to the original.

Eg:

Table: Exams: Columns: ExamID, ExamTitle, ExamCreatedDate This table would only store information relevant to an exam, such as the title of the exam, the exam's attributes and categories.

Table: Questions: Columns: QuestionID, QuestionTitle, QuestionValue This table would store only the information relevant to the question itself.

Table: ExamQuestions: Columns: ExamID, QuestionID, QuestionValueOverride (made up)

This table would store the ids for both the previous tables and any information that is specific to that exam's instance of that question.

This would allow you to write queries such as "How many questions belong to one exam"

select e.title, count(*) from exams e join examquestions eq on eq.examid = e.examid join questions q on q.questionid = eq.questionid