• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1953
  • Last Modified:

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-->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!
2 Solutions
Sounds like you only need a intermediary table between students and department.  If each "department" is linked to a specific school, then you can always determine the school based on the department.  
However, it really depends on what information about a department (within a University) you want to track.  If you simply need the NAME of the department, then yes, I would suggest an Association table between your University Table and a very simple Department table (since a MAthematicve Department could be found at Many UIniversities.

However, if you need to track specific information about the mathematics department at each University (such as Department Chairperson, Level of the courses offered, etc) then you would have a different depatment (though each named Mathematics for each university( which would the alleviate the need for a table between University and Department, since this model would hold the UniverityID as a field in the Department Table.

What you really wnat to avoid, if possible, if to have your depatmnent table hold multiple records that are essentially Identical ( a simple depatment table would hold DepartmentID, a UniversityID and a Department Name - thus multiple Mathematiccs department would be represented by very similar records in the department table, which could be significantly simplified by a University_Department table and a very simple - DepartmentID, DepartmentName -- Department Table)

On the other hand, if the Department table holds many fields which might be significantly different from one University to the next, then you would be much better off to have a more complex Department table (which would hold the UniversityID as one of those fields), and do away with the University_Departmnent table).  It all depends on the nature of what information about the Student relationship your are attempting to model in the application.

vpradminAuthor Commented:
Sorry for being so long in getting back.

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.

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now