1 of 1 people found this helpful
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:
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.
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.
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.
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...
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.