5 Replies Latest reply on May 22, 2017 4:30 PM by gswest

    Trying to Clean Up and Fix Gradebook Database


      So I've come pretty far along on my Gradebook Database. I've used join tables but they oftentimes aren't functioning as expected. The Gradebooks is usable at this point but has several issues.


      The first is...I'd like to be able to get Class Averages, Max, Min for Assignments to go on this Tab - Course Grade Breakdown


      Gradebook 2 Course Window Grade Breakdown.png


      Here's the Database Relationship Diagram:


      Gradebook Relationship Diagram.png

      Here are a couple of images that might help:


      Gradebook 2 Course Window Students.png

      and one last image:

      Gradebook 2 Student Window.png

      The tiny Class Assignments window in the top right corner is there because I can't figure out a way to automate Assignment information - DateDue, Points, Etc.


      Any help would be greatly appreciated.





        • 1. Re: Trying to Clean Up and Fix Gradebook Database

          There are probably multiple issues you need to address, but the most obvious to my quick glance concerns join tables. You say: "I've used join tables but they oftentimes aren't functioning as expected", but I see no actual join tables on your graph, so perhaps you misunderstand the term, and this is no doubt why they are not giving you the result you expect.

          In most databases there will be examples of many-to-many relationships. Central to yours is students—classes: any student may be a member of many classes, and any class will comprise many students. Your direct connection between Students and Classes is many-to-many, so it must be broken down into two one-to-many joins. You need to create a new table called, say student_JOIN_class, with just two fields in it: fk_ClassID and fk_StudentID. (Later you may add other fields, but those are the only ones you actually must have.) You connect students to classes via this table, not directly, as shown below:

          Screen Shot 2017-05-22 at 3.19.16 PM.png

          A join record will be created for each student–class pairing. Via the join table you will be able to see, from a Student record, all the classes that student is enrolled in; from a Class record, all the students enrolled in that class.

          1 of 1 people found this helpful
          • 2. Re: Trying to Clean Up and Fix Gradebook Database

            So in my case each student can only be in one class. I teach at a university and I don;t need this to handle a single student enrolled in many different courses. But I do need to be able to handle each course having multiple assignments, and each student then being able to access each of those. This is where the Join Table thing is crewing me up. I;ve watched the training video on this several times and it just isn't clicking. I'll see what I can do with your tip though. I think I may understand now. Only trying out it will truly tell.


            Thanks for the helpful comment though.

            • 3. Re: Trying to Clean Up and Fix Gradebook Database

              Hmm, got the JOIN Table created and it looks like the one above Classes_JOIN_Assignments with _fkClassID and _fkAssignmentID. Not sure what to do with it now however.

              • 4. Re: Trying to Clean Up and Fix Gradebook Database

                The join table holds primary keys from each of the parent tables (for example, STUDENT and ASSIGNMENT.


                Then, using the join table, you can do a query using the tables these foreign keys refer back to (or set up a portal, etc.) to look at each of the 1:M relationships in your data:


                1. A student can have many assignments

                2. An assignment can be "assigned" to many students.


                keywords makes a critical point about M:M relationships. If you don't model those correctly, and take the time to do it, you'll be forever working around a basic data modeling issue with "clever scripts" (endless frustration).



                Using a real ERD tool that will work with FileMaker (like SQL Editor) will also help you visualize your data. The database design is the step many rush through...


                For example....


                • 5. Re: Trying to Clean Up and Fix Gradebook Database

                  So I was just looking back over this. Isn't my Student Assignments Table a JOIN table? If has an _fkClassID and _fkAssignmentID. Each student can have many assignments and one type of assignment can be completed by many students.