4

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

@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.

Improve this question

1 Answer 1

Reset to default
1 
Taxonomy -> TaxonomyTerm
TaxonomyTerm -> Subject
Subject -> Topic
Topic -> Question

Each of these lines is a 1 to many relationship, e.g. A single Taxonomy can have 1 or more TaxonomyTerms, and these terms can be related to multiple Taxonomies. I think you mostly have it but I'm not seeing a need to link one TaxonomyTerm directly with another.

Improve this answer
2
  • How do you suggest to maintain relations between taxonomy terms. The need for linking one taxonomy term to another is that it is terms which are linked to each other and not taxonomy. Taxonomy is an abstract relationship whileas taxonomy terms define the actual relation between terms of different taxonomies. How do you see it differently. Please share your thoughts.
    – Ehs4n
    Commented Jan 10, 2014 at 4:17
  • I can find all TaxonomyTerms which share a common Taxonomy or Subject. Perhaps this does not mean they are directly related. Within the example you provided there were not multiple TaxonomyTerms directly related so I assumed you misspoke at some point. Should you need to directly relate them you have the correct idea of a relation table, a table with two foreign keys to the TaxonomyTerm table, one for each term
    – Kevin
    Commented Jan 10, 2014 at 4:55

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.