I am trying to store information about a school and the class timetable in a database. The outline is as follows:
'A subject has ~20 lessons, each of which needs to have it's description stored in the database. The topic of each lesson is also kept.
Each topic is also taught by a different teacher(s) - some topics are taught by multiple teachers. Each teacher only teaches a single subject and topic.'
So far I have this:
topics: (id (PK), topic_name) subjects: (id (PK), subject_name) lessons: (id (PK), lesson_desc, subject_id (FK), topic_id (FK)) teachers: (id (PK), teacher_name, subject_id (FK), topic_id (FK)) teachers_and_lessons: (teacher_id (PK), lessons_id (PK))
However, my concern is that it does not enforce certain constraints, and information is perhaps being unnecessarily duplicated.
For example, there is no enforced constraint against me adding a (teacher, lesson) pair to
teachers_and_lessons where the teacher's
subject_id is 4 but the lesson's
subject_id is 5.
Assuming the data is valid, you can also infer the the
topic_id of the lesson or the teacher by using
teachers_and_lessons and either the
subjects table. For example, a teacher with record (1, roger, 1, 2). If the record (1,3) exists in
teachers_and_lessons, then the lesson with id = 3 must have subject_id = 1 and topic_id = 2. Note also: a lesson or teacher should be able to exist without having been assigned anything.
Does anyone know a better way of structuring my database? Thanks