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)
MAT is a taxonomy term for
MAT may have two subjects viz
General Knowledge which are taxonomy terms for
Reasoning subject will have 10 different topics with taxonomy terms falling under
Topic taxonomy, say
Reasoning will have
Topic B ....
Question will belong to a
Topic which in turn will belong to
Subject which again will belong to
Subject may belong to multiple
Exams or a
Topic may belong to multiple
Is this the correct approach to create db schema for this system.
@MirNazim suggested another approach to this problem with
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.