Mysql – Relation between multiple hierarchical taxonomies linked to a single entity

database-designMySQLschema

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.

Best Answer

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.

Related Solutions

Unenforced constraints/possible duplication in the database design

Your model includes referential cycles. This comes up quite a bit when you're modeling and you haven't worked through the differences between what relationships exist and what relationships are important to your system.

Without knowing more about your business rules and their justification, I would suggest that you consider dropping teachers.topic_id and possibly also teachers.subject_id.

Your lessons table relates subjects and topics. Since you have a many-to-many relationship between teachers and lessons, you can use lessons (via teachers_and_lessons) to tell you which teachers can teach which topics and which subjects.

I doubt that neither teachers.topic_id nor teachers.subject_id are correct anyway, since this would imply that any given teacher can only teach one topic and one subject. I don't know about your school, but it sounds much too specialized to me!

Edit: Recognizing Teaching Qualifications

Based on a comment by OP (below) the question is how can a teacher be recognized as being qualified to teach a topic without there being an actual lesson assignment made (yet)?

Issue 1: The relationship of topics to subjects:

It isn't clear from the original question whether topics belong to one subject or possibly to multiple subjects. If topics (e.g. fractions) belong to only one subject (e.g. math) then there should be a foreign key like so: topics.subject_id. If this were so, then lessons.subject_id would be redundant and could also be dropped. This would simplify the model and make things much clearer while reducing the risk of inconsistent foreign keys.

Issue 2: What teachers can do vs. what they are doing:

The teachers_and_lessons table shows which lessons teachers are actually teaching. Before this assignment is made, OP would like to be able to record which topics a teacher is qualified to teach. This could be done with a second M:N intersection, like so:

teacher_qualifications:
(teacher_id (PK/FK), topic_id (PK/FK))

This design assumes the simplification suggested under Issue 1, above. One potential drawback of this simplistic solution is that there is no constraint against assigning a teacher to teach a lesson for which they are unqualified. This constraint, if it is important, could be accommodated by replacing the teacher_qualifications and the teachers_and_lessons tables with new tables as follows:

teacher_qualifications:
(qualification_id (PK), teacher_id (FK,UN1), topic_id (FK,UN1))

lesson_instructor:
(qualification_id (PK,FK), lessons_id (PK,FK))

Again, these tables assume the simplification from Issue 1. Even without that simplification, these tables could be amended to use an analogous approach.

Topics and posts represented by one table or two

Generally...

A a rule of thumb: don't pre-optimize for performance. I think a lot of developers assume that joins are inefficient and they don't trust DBMS to do what it's built to do.

Start with a properly normalized design. Make sure your indexes and queries are optimized for your particular balance of reads and writes.

If and when you start to find that performance can't keep up with the best hardware you can afford, then start to think about denormalizing.

If you denormalize early you are just setting yourself up for maintenance headaches down the road.

More Specifically...

Looking at your suggested table layouts, I would suggest that you're trying to make TOPIC do too much. Anything which could appear in POST (e.g. poster_id) almost certainly doesn't belong in TOPIC. I suggest you adjust your thinking a little bit. I get the impression you are thinking very much about what topics and posts are going to look like on the page. This may be leading you to thinking of topics as a small super-set of posts, whereas they are probably rather more like subject headings. The fact that you plan on displaying the first post under each subject heading along with the heading isn't a good reason to co-mingle posts and headings.

I think you may want to rethink some of your cumulative total columns too. Thinks like up and down votes may need to be tracked in their own tables. You may need to do this to keep people from up or down voting repeatedly and to allow people to rescind their votes. Similarly, you may want to know all editors, not just the last editor.

UPDATE

Best Answer

Related Solutions

Unenforced constraints/possible duplication in the database design

Topics and posts represented by one table or two

Related Question