Database Design – Recording Occurrences in TV Show

I agree with TomTom on the merits of the Datamodel resource book - it will give you more insight than StackOverflow, and if memory serves, there's a fairly detailed discussion of exactly this scenario.

The design you've come up with is similar to what I've seen for other systems - what you call ProductVariation is often called "variant".

I'm a little confused about the role of SKU in your design - the acronym stands for "Stock Keeping Unit". As it's supposed to be unique and never change, I'd make that the primary key of your ProductVariation table, rather than ID.

If you're only ever going to sell your current product range, or are happy to refactor in future, you can leave it as is; alternatively, you could consider a design with "table per subclass". This would give you a schema along the lines of:

• Product (id, name, categoryID, description)
  • SizeVariant (SKU, productID, finish, width, thickness, Size, location, quantity)
  • ColourVariant (SKU, productID, Colour, finish, location, quantity)
  • NoVariant (SKU, productID, location, quantity)

Note how I've also put some of the attributes you've currently assigned to "Product" into the child tables.

This keeps the schema more "self describing", but adds a lot of work - only do that if you think the product catalogue will grow in the future, and you want to avoid EAV.

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.