I have a feeling this is an instance where a self-join might be my best bet, but I'm hoping otherwise.
This is a school report card database that needs to have transcript functionality.
I realize this looks kind of crazy, but for right now I'm concered with Students, Enrollments, and RCData.
Students is going to list every student at the school, both active and graduated (up to a point). Enrollments is a join table of a student to a given course in a specific school year. So every student should, with a full course load, have 9 enrollment records per year.
RCData tracks grades per marking period, of which there are 3 per year. In other words, every year, a student will have 9 enrollments and 27 RCData records. Teachers are sent to a filtered list of blank RCData records for grade input.
The issue is that for report card and transcript generation, I need to be looking at RCData records vis-a-vis Students. However, when I make a portal from Students to RCData, there is no direct relationship, and so the appropriate data do not display.
My current thoughts are:
- Do away with the the RCData table altogether, and combine it with the Enrollments table. However, I would prefer to keep them separate, so I don't have redundant fields in the Enrollments table.
- Attempt a cascading portal setup, where RCData records display on a Students layout depending on the active portal row in a portal to Enrollments.
- A self-join of RCData accessed by a GTRR operation from Students/Enrollments.
Any help would be greatly appreciated.