Simple database schema

Posted on 2007-10-18
Last Modified: 2012-05-05
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!
Question by:vpradmin
    LVL 12

    Accepted Solution

    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.  
    LVL 44

    Assisted Solution

    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.


    Author Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    In today’s complex data management environments, it is not unusual for UNIX servers to be dedicated to a particular department, purpose, or database.  As a result, a SAS® data analyst often works with multiple servers, each with its own data storage…
    I guess that all of us know that caching the data usually increase the performance, but I worried if all of us are aware about the risk that caching the data provides and how to minimize this.  That’s the reason why I decided to write this short art…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now