I feel stupid for asking this question because I should know the answer after working with databases on and off for several years but I'm drawing a blank for some reason.
I have three things I'm trying to relate--People, Schools, and Departments. We want to keep a historic record of every School (university name) a person has attended and each department at that school they've worked at. I know that between "People" and "Schools" I need an intermediary table since this would be a many to many relationship (a person can have a history at many schools, and a school can contain many people). Therefore I have a table in between the two called "School Association" which has two foreign keys--one that related to each table.
My question is do I need an intermediary table in between "Schools" and "Departments"? Each school can have many departments but a department must belong to a single school. Departments would be like "Math", "Anthropology",etc. I tried doing a relationship like this but am having trouble with it:
People-->School Association-->School-->Dept Association-->Department
So as you can see "People" contains the names of people, "Schools" the names of universities, and "Departments" the names of departments within a single school (university). The "School Association" contains two foreign keys--one to relate to "People" and one to relate to "Schools". "Dept Associaton" contains two foreign keys--one to relate to "Schools" and one to relate to "Departments".
Am I missing something obvious here? I know if I were talking about lets say "Schools" and "Organizations" where the "Schools" and "Organization" weren't directly unrelated to each other this would be easy. You would want a table relationship like:
Organization<--Org Association<--Person-->School Assoc-->School
However in the above example if each school actually HAD separate organizations within each school, and each organization belonged to just one school (which would be like my "Schools" and "Departments" example) then I can't figure out for the life of me how to make it work. Any help is much apperciated!