-1

I am going to create an multiple choice based examination system. I am new to database design and want your help to answer a few questions. How to model questions and questions category?

Context: There are multiple subjects(for eg: physics, chemistry, math etc). Each subjects have sections and each sections have chapters. For example: Chemisty have sections: physical chemistry, organic chemistry, and inorganic chemistry. Each section have related chapter.

And there are questions and each questions fall to one of the chapters. By above context, it is clear that Each chapter belongs to a unique section of a unique subject. Now how should I go on designing the database for this?

option_1:

questions:
question_text varchar,
...
chapter_id int foreign_key

chapters:
chapter_name varchar,
section_id int foreign_key

section:
section_name varchar,
subject_id int foreign_key

subject:
id int primary_key,
subject_name varchar,

But I think this will lead to complex query when I want to find questions by subject and sections. First I need to find all the sections and then all the chapters and then find questions that belongs to given questions.

option_2:

questions:
question_text varchar
subject_id int foreign_key,
section_id int foreign_key,
chapter_id int foreign_key,

subjects:
subject_id int primary_key,
subject_name varchar

sections:
section_id int primary_key,
section_name varchar

chapters:
chapter_id int primary_key,
chapter_name varchar

In this case, there will be no relation between subjects, sections and chapters.

I would love to hear some insights.

1 Answer 1

1

Option 1 is normalized solution. Normalized solution is good to start with. I don't think the query is complex, to hide complexity you can use view.

Option 2 is de-normalized. It offers better read performance with the risk of inconsistency with updates. (like if you move chapter to different subject, don't forget to update questions table)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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