Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 subject_id and topic_id of the lesson or the teacher by using teachers_and_lessons and either the lessons or 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

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
Yes, although it may seem strange, there is a constraint a teacher can only teach one topic and subject for my particular model. I have a question about your suggestion if I may. What If I wanted to add a lecturer to the system and would like to note that they teach the topic 'fractions' in the subject 'math'. It seems like I would be powerless to do this until he was assigned to a lesson –  roger smith Oct 21 '12 at 23:36
    
@rogersmith One of the issues with your model is that it doesn't recognize the relationship between a subject and a topic, unless there is a lesson for it. See my edit for a possible solution to this. –  Joel Brown Oct 22 '12 at 0:22
    
You're not going to like this Joel, but topics can belong to multiple subjects haha. Nevermind though, I understand your approach and I'm pretty sure I can make it work. Thank you very much for your help! :) –  roger smith Oct 22 '12 at 0:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.