Your upper Table Occurrence group looks like a good start, but I would not match Teacher to CaseManager by name. Names are not unique. People change their names and trying to correct a data entry error in a name can be tricky to do. Match by an ID value (serial number or UUID) instead.
You haven't indicated where you are having trouble exactly.
Am I correct that a given consultation pairs one Case Manager (teacher) to one student to one response?
If so, you can either set up a join table between Teachers and Students with fields to record the consultation response. Or you might set up a join table to which you link Student, Case Managing Teacher and Response tables.
But on closer look, you are also involving the student's set of "goals" records. What role do they play in the consultation process?
Yes, thank you for noticing that teacher name connection to case manager.
A single consultation pairs a teacher (not case mgr) to one student with responses to each goal. (almost survey/questionaire style). In a single consultation a teacher will answer a few questions. A list of student goals is presented.. with a mastery %.. Did they meet the goal? Yes, No, Rarely, Often type response. Then a number given for Absence, Tardy, and Grade.. then comments.
The Case Manager is the liason between teacher and student. They compile the data and submit to the state. Each student is assigned a case manager (which is a teacher and possibly one of the students teacher that will fill out their own consult rpt)
As for goals.. Each student has x number of goals. So, I would want the layout to display only that teachers students and only the selected students goals. Then the teacher will respond to each goals with the previous answers.. yes, no etc.
I cant tell if I am over thinking it or if I need to think completely different about it.
I would combine the join table and the Consult table
csltTeacher::__kpTeacherID = csltStuTeacher::_kfTeacherID
csltStudent::__kpStudentID = csltStuTeacher::_kfStudentID
csltStuTeacher::__kpStuTeacherID = Responses::_kfStuTeacherID
Goals::__kpGoalID = Responses::_kfGoalID
Ok.. That completely makes sense combining the join table with the consult table.
Now, one thing I don't understand is the goals relationship.. The goals are student related... in the sense that each student has a certain number of goals. I did have this in the student table at one point but because it could be 1 goal or 10...I broke it out.. This is getting down to what I don't understand.. the responses are related to the goals also but only through the consultation report from the teacher.
Attached is the screenshot of the spreadsheet that each teacher receives for each student that has a consultation. This might give you an idea where the whole idea came from to create a database for it. A teacher would sometimes get a spreadsheet with 15 or so student forms in it. Then the case managers would be piled up for days going through them.