9 Replies Latest reply on Feb 8, 2012 9:11 AM by ChatAtkins

    New School, New Ways, New Database, Old Teacher!

    ChatAtkins

      Title

      New School, New Ways, New Database, Old Teacher!

      Post

      Hi There,

      This is my first time here and yes I am a newbie.  Our school wants to develop a program where we put everything about our classes on to a database.  I opened my big mouth and suggested we use Filemaker Pro which I have.

      Everyone liked the idea and the Principal told me to develop a prototype.

      Here is the setup that I think will work but I'm not sure how to connect the dots or set up the relationships:

      Students Table

      StudID (Primary Key)

      fname

      lname

      etc.

      Classroom Table

      ClassID (Primary Key)

      ClassNo

      ClassSubject

      Etc.

      I understand that because a Student can have many Classes and a Classroom can have many students I need to have a table in between that relates to both as in:

      Stud_Class Table

      Stud_ClassID (Primary Key)

      fk_StudID (Foreign Key to Student Table)

      fk_ClassID (Foreign Key to Classroom Table)

      However, here is where I am stuck, a Class can have one or more Sections AND each Section has to have a Daily Report associated with it.  What I came up with is this:

      Section Table

      SecID (Primary Key)

      SectionName

      SectionNo

      SectionLength

      etc.

      Daily Report Table

      DailyReportID (Primary Key)

      DailyRptDate

      DailyRptTeacher

      etc.

      I’m not sure where to relate the Daily Report Table.  Should it be related directly to the Section Table?  Since the Section Table is only providing information about the part of the Course Table.

      OR should I relate the Daily Report Table to the Section Table and relate the Section Table to the Course Table? 

      Sorry for the long post, but I'm really stuck and any help would be greatly appreciated,

      Chris

        • 1. Re: New School, New Ways, New Database, Old Teacher!
          ChatAtkins

          I guess I should clarify, and as you may have already guessed, yes, my name is Chris Atkins, but since I am rather longwinded everyone calls me Chat Atkins!

          • 2. Re: New School, New Ways, New Database, Old Teacher!
            philmodjunk

            Instead of linking students and classes, you can set up your join table to link students and sections and then sections links to classes:

            Students----<Student_Section>-----Sections>-----Classes   (>---- means many to one)

            I'm not totally sure you even need a daily report table here. What will this report contain when one is produced?

            • 3. Re: New School, New Ways, New Database, Old Teacher!
              ChatAtkins

              Hi PhilModJunk,

              Thanks for your answer.  To give a little more detail: Our school has an apprenticeship program so on the Daily Reports there are a number of areas that a student must pass.  So the teacher uses lookups in each area.  The lookups put in details about each area and the required grade.

              The teacher then puts in the appropriate mark and the system then grades the student accordingly.

              The reports that are needed are:

              Class: which shows dates and times, teacher and a portal listing all of the students.

              Teacher: which lists teacher name, the classes assigned, dates and times and students in each class

              Daily Report: which shows student name, Apprentice Level, 12 areas in which there are lookups and once clicked a grade is shown next to the task and another field which the teacher fills in a grade based on performance.

              Student: Student Name, classes taken, grades etc.

              BTW, we are also using FM Go on an iPad so the teacher can walk around to evaluate the student which is then transferred to a Windows computer to compile the statistics.

              I hope I haven't made this clear as mud!

              • 4. Re: New School, New Ways, New Database, Old Teacher!
                philmodjunk

                The reports that are needed are:

                Class: which shows dates and times, teacher and a portal listing all of the students.

                If you intend to print this report or make a PDF of it, don't use a portal. A portal will limit you to a list that is only as long as the rows you specify for it. Instead, a report listing all students enrolled in a section and/or a class can be produced from a layout  based on Student_Section. This can be a listing for just one section, one class or more than one section/class.

                Teacher: which lists teacher name, the classes assigned, dates and times and students in each class

                You'll need to either add a Teacher table and another join table so that you can assign teachers to sections just like we have for students. And a report based on this Join table can list all sections and/or classes assigned to just one teacher or the entire staff.

                Daily Report: which shows student name, Apprentice Level, 12 areas in which there are lookups and once clicked a grade is shown next to the task and another field which the teacher fills in a grade based on performance.

                Ok, you'll need your report table for this one, also an "areas" table linked to classes so that you can specify the areas for each class. This report table should be linked to the Student_Section table so that you can record student progress for each class that a student is taking.

                Student: Student Name, classes taken, grades etc.

                You this is another report that can be based on the student_section table where you already have one record for each student. Likely a data field can be added to this join table to record the final grade. (It could be done with a calculation field, but this would make it difficult to assign a different grade from that specified by the calculation should the need arise to assign a different grade from that computed directly from the database.

                BTW, we are also using FM Go on an iPad so the teacher can walk around to evaluate the student which is then transferred to a Windows computer to compile the statistics.

                Ideally, you should set up a computer with FileMaker Pro or FileMaker Server to host your database with WiFi access set up so that each Teacher can use their iPad as a client of the same hosted database and thus enter and retrieve data directly into/from the same hosted database file.

                • 5. Re: New School, New Ways, New Database, Old Teacher!
                  ChatAtkins

                  Thanks again PhilModJunk,

                  So if I have this right:

                   (>---- means many to one)

                   (----< means one to many)

                   

                  Students----<Student_Section>-----Sections>-----Classes

                  Student_Section------<Daily_Report-----<Areas

                  Sections>-----Teachers


                  So far we have been using iTunes to download the test database material back and forth between FM Go an Filemaker Pro Advanced on the Windows computer.  It is working well, and hopefully if I can get the rest of this to work well, our school will buy Filemaker Server.

                   

                  • 6. Re: New School, New Ways, New Database, Old Teacher!
                    philmodjunk

                    Do not try to use iTunes to synch this database. Each copy will overwrite the previous so you will be unable to use it to combine added/changed data from multiple iPads. This is a major recipe for disastor here.

                    I'd make one change  here. I would assume that areas might differ for each class. If so, the relationship for areas would be:

                    Classes----<Areas

                    Otherwise, you are limiting yourself to the same list of areas for every class

                    It's possible that you might need a join table between reports and areas and then you'd base your report layout on the join table.

                    • 7. Re: New School, New Ways, New Database, Old Teacher!
                      ChatAtkins

                      Once again, Thanks PhilModJunk,

                      I appreciate all your help, especially trying to get multiple iPads to work together, didn't think about that one!

                      One other point which I forgot to mention, once a Student has passed all the Classes, he then becomes a Teacher, so would I use another Table Occurence of the Students table but have a field that states his change of status?

                      Thinking about the above, I guess I could change the Students Table to Employee Table and have a field which states if an Employee is a Student or a Teacher and have two TO's: Students and the other TO called Teachers.  Do I have that right?

                      Chris

                      • 8. Re: New School, New Ways, New Database, Old Teacher!
                        philmodjunk

                        Sounds like you have a good grasp of the situation. Any time you find that you have two tables that store the same infor or nearly the same info, you need to see if you can use one table with some kind of "type" or "label" field in place of using two separate tables.

                        • 9. Re: New School, New Ways, New Database, Old Teacher!
                          ChatAtkins

                          All I seem to be doing is thanking PhilModJunk,

                          I really appreciate all your help.  One last point, I did try posting this same question on another "forum", but instead of getting an answer all I got was derision and ridicule.  

                          Thanks for being so patient with a newbie.

                           

                          Chris