Relationship Graph Setup Puzzler

[Rich S]

Hi all:

I'm stuck on how to set up the relationships/tables connections for this scenario and need your guidance:

It's for a school system where they have Districts, Schools, Staff (Name), Roles, Grades and Subjects. Where I'm stuck is, let's say a staff person is a Counselor—she can be a counselor for the a district or one or more its schools. She can also have more than one role, e.g., paraprofessional, at a school (within the same district), and each role can apply to one or more grade levels and subjects, e.g., she can be a counselor and a teacher that teaches different subjects at different grade levels.

I've attached a crude map of what it is so far, but I'm not sure I'm on the right path...and I'm sure additional T.O.s need to be added (between District, School and Staff-Role Join?), but I don't know.

TIA for your help.

 

Relationship.docx

[comment]

19 hours ago, Rich S said:

she can be a counselor for the a district or one or more its schools.

Databases work best with regular structures. That's not to say that an inconsistency such as this cannot be accommodated, but it won't be ideal. A lot depends on what do you actually intend to produce out of the data entered.

I would probably opt for a "star" join table of Roles joining Staff (or StaffAssignments) to both Districts and Schools. And if a role can apply to multiple schools in a single district I would consider using a checkbox field to select the applicable schools - provided that it wouldn't conflict with some reporting ability you may want to provide.

Please note that we are discussing an ERD, not the relationships graph. That will be a whole another issue.

 

Edited 17 hours ago by comment

[Rich S]

Thanks! A star, versus spaghetti, is always preferred. : )