Hi everyone:

This is my first post to the forum, so please excuse me if I don't quite have the hang of framing my questions in the most useful way.

I'm working on a db to track information for about 200 academic programs. I thought I had my tables and relationships set up well, but then I ran into an issue while testing with real data.

The basic problem is this: I have several entities that can be related in different ways or not related at all. Institutions; Schools/Colleges (within those institutions); Departments (may be part of a school/college or may stand alone under an Institution); and Programs (will always be part of a school/college, department, or school/college AND department). Each program has a subset of unique information—contacts, director, finances, student population, admission requirements, etc.

Some of this information will change each year, but I want to maintain historical data.

Institutions will always house one or more schools/college AND/OR departments.
Programs may be situated within a single school/college; a department within a school/college; OR be jointly administrated by two or more schools/colleges or schools/colleges & department combinations.


Example 1:
Institution: University of Bob
Program: Master of Health Administration (MHA)
School: Public Health
Department: Health Services Administration

Example 2:
Institution: University of Bob
Program: Master of Health Administration/Master of Public Administration joint degree (MHA/MPA)
School & Department:
1) Public Health, Department of Health Administration
2) School of Public Affairs (no department affiliation)

Example 3:
Institution: University of Joe
Program: Master of Public Health (MPH)
School: School of Global Public Health
Department: None

So, to recap:
1) Institutions will have many schools/colleges, departments, or schools/colleges & department combinations.

2) Programs may belong to one or more schools/colleges, departments, or schools/colleges & departments combinations.
3) I need to track information by academic year.

I’ve attached a screen shot of my current tables and relationships. I realize I have fields that need to move, but right now I'm focused on this relationship setup.

As I’ve written this, I’m thinking what I need is to separate Institutions into their own separate table (with just pkInstitutionID, InstitutionName, FileAs) and have one table that will capture the both schools and departments (tblSchoolDept), then create a junction table between programs and the school/dept table so I can assign more than one program to a school/department combination and vice versa.

Do you think that’s the best option? I think I’ve gotten myself unnecessarily confused.

Finally, I am not sure the best way to go about adding records for a new academic year. The bulk of information for each program will remain the same, but almost any program detail could change in any given year, which is why I put the “AcademicYear” field into the program table, thinking it’s probably best to create a new record each year. Any other suggestions?

Thank you in advance just for reading through this. Writing it out has already helped.

Best regards,
Shanon