Hi - New to database design here, and I've got a project that is stumping me. The real life situation is too complicated to get in to here, but I have a simple anology that fits very well indeed. Here is that analogy:

I need a way of storing data relating to teachers, students, and the subjects that they teach and study. Sounds simple enough.

Here are a few facts about the relationships between these parties:

* Students learn multiple subjects.
* Students have multiple teachers.
* Subjects have multiple teachers.
* Teachers have multiple subjects.
* Teachers have multiple students.

I would sum that up as a many to many to many relationship. Does that make sense?

If so, how would I go about storing that data in a simple, scalable way. ie. the best way.

So far I have the following tables:

Students
-----------
student_id
student_firstname
student_lastname
student_dateofbirth
etc...

Teachers
-----------
teacher_id
teacher_firstname
teacher_lastname

Subjects
----------
subject_id
subject_name

Can anyone give any suggestions about how to go about storing the information about which subjects teachers teach, and which subjects students study? Do I need link tables? What would they be called, and what data would they store?

Feel free to ask any questions to clarify - I'll be listening. Thanks in advance.

Andy.

When a teacher engages as a subject, it is a class. Therefore you need to track classes:

tblClass:
class_id
teacher_id
subject_id

When a student takes a subject, they are effectivelyt aking a class, so you need to track that in a seperate table:

tblClassStudents
class_student_id
class_id
student_id


That should take you most of the way...

Ahh... something's just fallen into place. Thank you very much for that. I will go away and get my head around it properly with those new concepts to consider.

Thanks again.