vpradmin
asked on
Simple database schema
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-->Dep
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-->Sch
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!
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-->Dep
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-->Sch
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
One thing I should mention is that I am working with an existing database I've inherited. The way its currently set up it has a table called "Association" that essentially servers to join all three tables, thus it contains three foreign keys--a "Person ID" (to link it to the "Persons" table), a "School ID" to link it to the "Schools" table, and a "DeptID" to link it to the "Department" table. I thought surely there is a better way to do this...but perhaps not.
Another thing I should mention is that though every person has a "School" listed, not everyone has a "Department" listed--so I don't think only having a table between Persons and Departments would work. What's more "school" and "department" have their own addresses. So I think I'm probably going to have to just keep the structure the way it is. Thanks anyway guys.
You can consider this question resolved.