For two years I've been developing a solution for a school that I run. See attached screen shot for the relational graph that I'll refer to here to describe a problem that I have not been able to solve this whole time. Perhaps it's not solvable with the way I have set things up.
Like any school, my students sign up for classes, but my students enroll in a program that consists of a set of classes, so rather than signing them up class by class, I enroll them in one package or another. I assign which classes go with which packages. [To keep all $ charges in one table, I also have packages for supplies and fees and other things that don't have any classes associated with it--this is a bit dissatisfying as a DB designer but it's "probably" not relevant to my problem. ]
The current solution does just about everything I want it to do, including:
--Assign students to particular "packages" [See pink S_Enroll_JO near the top of the graph]
--Assign classes to "packages" [See beige B_Packages_PackagesClasses_JO near the bottom, and then copied again for reference in beige near the top as S_Enroll_Packages_ClassPackages_JO, which allows me to see which classes a student is signed up for (indirectly) given the packages that they signed up for (directly).] Each class has an "hours" field since every class has a different number of credit hours.
What the solution doesn't let me do is keep a record of how many hours each student ACTUALLY earns since some students don't achieve the full amount for the class for one reason or another. Ultimately I'd like this solution to be able to spit out transcripts for me so I'm not manually doing them in Word, but to do that, I need to have each student's hours for each class associated with that class. My current setup doesn't give me a way to do that because the Packages table is in the way or in between them relationally.
I can't think of any way for me to add to the relationship graph to make this work.
I know that I could build the solution so that I have to enroll each student in each class separately. Obviously that would give me a place to put the students' actual hours for each class, but this would