I am working on an online test application which supports multiple choice questions.
I have created schema for taxonomy (classification) system to be used with questions.
taxonomy(id, name, slug, description)
taxonomy_terms(id, taxonomy_id, name, slug, parent, weight)
question_taxonomy(id, question_id, taxonomy_term_id)
A question is classified under various categories (taxonomies).
At the top rests Exam taxonomy which has Subject taxonomy as children. Each Subject consists of various Topic taxonomy which may further be divided into Sub Topics by adapting Sub Topic taxonomy.
To create relationships between various taxonomy terms(linked across taxonomies), I have created another table as
taxonomy_term_relations(id, parent_id, child_id)
e.g,
MAT is a taxonomy term for Exam Taxonomy
MAT may have two subjects viz Reasoning & General Knowledge which are taxonomy terms for Subject taxonomy
Reasoning subject will have 10 different topics with taxonomy terms falling under Topic taxonomy, say Reasoning will have Topic A, Topic B ....
Each Question will belong to a Topic which in turn will belong to Subject which again will belong to Exam.
A Subject may belong to multiple Exams or a Topic may belong to multiple Subjects.
Is this the correct approach to create db schema for this system.
UPDATE
###UPDATE###
@MirNazim@MirNazim suggested another approach to this problem with Exams, Subjects and Topics treated as separate entities rather than taxonomy, which will reduce the complexity of whole thing. So the new structure may become
exams(id, name, description...)
subjects(id, name, description ...)
exam_subjects(id, exam_id, subject_id)
topics(id, name, parent, weight....)
subject_topics(id, subject_id, topic_id)
Any suggestions on this approach will be appreciated.
