I need help designing the relationships of my database...
I will try to explain my database and what I hope to do with it as clearly and briefly as possible:
Here are the different tables I have: Films, Contacts, Courses, and Streaming Requests.
A Course has at most two Contacts listed as the instructor(s). A Contact can be the instructor for multiple Courses.
The Streaming Request is when a Contact requests a Film to stream. When the Contact makes the Streaming Request, the Contact must also specify the Course the Film is streaming for.
Therefore, a Streaming Request is related to one Film, one Contact, and one Course.
Films, Contacts, and Courses all have portals to keep track of Streaming Requests that are related to them.
So, here's where my problem lies. Everything is related properly except for the Contact-Course relationship. Instead of displaying the one (or two) Contacts related to a Course, it is showing the Contacts who have made a Streaming Request for that given Course. So, if no Streaming Requests have been made for a Course, then the portal containing related Contacts is blank.
Or, I have another strange situation... Say Joe Brown and John Smith are the two Contacts listed for a Course, French 101, as instructors. If another Contact, Peter Parker, makes a Streaming Request for some film and the French 101 Course, then Peter Parker is suddenly listed in the related Contacts portal on the French 101 record. If Joe Brown and John Smith have made no Streaming Requests for French 101, they are not listed in that portal.
So, in other words, the Contacts portal in Courses is not showing the Contacts listed as instructors, but Contacts who are related to that Course's related Streaming Requests. Furthermore, if you look at the Courses portal under a Contact, it doesn't show Courses that Contact is an instructor for, but Courses that Contact has made a Streaming Request for. I hope that makes sense...
I am sure I am not understanding how to properly use separate instances of tables when it comes to creating relationships. Does someone know how to structure these relationships so everything will display properly? I'm an amateur to FileMaker Pro and this has had me stumped for a while.
Thanks in advance to anyone who has any advice.
EDIT: PhilModJunk, I added a screenshot of my relationships thus far. I tried incorporating your suggestion and right now I am reading about how Join tables work, as I haven't heard anything about this before. Thanks for the advice so far... Are there any quick suggestions on how to make this Join table thing work?
RE-EDIT: I think I understand this actually. Thank you very much!