0

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

Questions Table

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.

  • 3
    Third option: revisit normalization and table relationships - you don't need to store CSVs in columns at all. You're looking for a many-to-many relationship in these tables to get your desired results. I'd suggest making question options & answers also separate tables so you don't lock yourself into having 5 and 2 of each, no more no less (or utilizing a lot of NULL values in the case of less). – LowlyDBA Apr 6 '16 at 16:26
  • 1
    Was going to write an entire answer bu @JohnM is 100% correct. If exams share questions then a many to many relationship is the best way to model it (Exams -> ExamQuestions <- Questions). Also consider making the child tables for questions, as a user's answers are unlikely to be the only answer for a specific question. (Exam has many questions, questions have many users, questions have many answers, users have many answers and questions and exams, etc. – ConstantineK Apr 6 '16 at 17:32
  • @hobs: Thanks!! Can you plz share an example of many-to-many for better understanding ... for the separation of questions, answers and options is a good idea... but for my requirement...I can go with it. The issue is How an Exam should decide what questions are included within it. – Vishal Kumar Apr 6 '16 at 17:56
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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