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:
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.