4 Replies Latest reply on Oct 24, 2014 7:39 AM by whhsdav

    Relationships Setup For Teacher Response Database

    whhsdav

      Title

      Relationships Setup For Teacher Response Database

      Post

      Hi All,

      I am new to the forum and have learned much from you all in the past. Thanks for that! 

      I have used mainly access in the past but a few project have made it necessary to use Filemaker Pro Adv.. I currently am using v12 Pro Advanced on the main development machines and one v13. (tryng it out).

      This project is a bit more complicated.  I am creating a student/teacher database for consultation reports. It has stumped me on relationships.. So, here is what I am trying to get.

             
      • Student Data:       
                    
        •   Name
        •           
        •   Grade
        •           
        •   Case Manager (which is a teacher: 1CM:ManyStudents ; 1Stu:1CM)
        •           
        •   Goals (1 Student many goals, 1 Goal one student)
        •           
        •   Mastery (1 for each goal)
        •           
        •   Notes
        •      
             
      •      
      • Teacher      
                    
        •   Name
        •           
        •   Role (Teacher or Case Manager)
        •      
             
      •      
      • Student Teacher Join Table (Many To Many)      
                    
        •   Block (1 Student can have many teacher based on block 1,2,3,4: Teachers can   have many student.)
        •           
        •   Subject
        •           
        •   Term (Q1,Q2 or Q3,Q4)
        •      
             
      •      
      • Consultation Reponses from Teacher (each teacher has many student consults)      
                    
        •   Goals Response (Met Mastery of Goals?, Yes, No, Often, Rarely)
        •           
        •   Absence
        •           
        •   Tardy
        •           
        •   Comments
        •           
        •   Grade Percentage
        •      
             

      I have posted what I have right now... it's a mess right now because I have been trying different things and just lost now.. lol  

      Thanks in advance for any advice.

      Dave

      tables.PNG

        • 1. Re: Relationships Setup For Teacher Response Database
          philmodjunk

          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?

          • 2. Re: Relationships Setup For Teacher Response Database
            whhsdav

            Thanks PhilModJunk..

            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.

            • 3. Re: Relationships Setup For Teacher Response Database
              philmodjunk

              I would combine the join table and the Consult table

              csltTeacher-----<csltStuTeacher>---------csltStudent
                                                |
                                                ^
                                         Responses>---------Goals

              csltTeacher::__kpTeacherID = csltStuTeacher::_kfTeacherID
              csltStudent::__kpStudentID = csltStuTeacher::_kfStudentID
              csltStuTeacher::__kpStuTeacherID = Responses::_kfStuTeacherID
              Goals::__kpGoalID = Responses::_kfGoalID

              • 4. Re: Relationships Setup For Teacher Response Database
                whhsdav

                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.