OK I feel like this should be easy but I have a little bit of COVID brain, can someone please help me figure out how to structure this database.
GOAL: I need to track student progress in a class in a portal like this:
I feel like I'm missing something stupidly simple, but I've been beating my head against this for an hour now and can't figure it out. If the list of assignments wasn't already a portal I'd just make a portal and filter it for studentID to show and modify status but I can't
Just to be certain I understand… the list of students is a list of students for a specific class… right?
Assuming I am right… then you are missing a join table: assignments for a student for a class. Every time you create an assignment for a course, you also need to create a record for each student of the course with the assignment with three keys: CourseID; StudentID; AssignmentID. To view assignments for a single student for a single course, you need to link from the list of students for the course to the join table with two predicates: CourseID and StudentID. This will give you the join records, which ideally contain grading and other information, and the course assignment record, which contains generic assignment information.
Things change a bit if my initial assumption is wrong: the list of students is for all students, regardless of course. The student table relates to the assignments for a student for a class table… only you will now display assignments for all courses for the student. Use sorting to group the results by course.
Hope this helps.
I have a join table, it got cut off in my screenshot. I only join it to assignment and student since every assignment is only in one class.
To view assignments for a single student for a single course, you need to link from the list of students for the course to the join table with two predicates: CourseID and StudentID.
If I make a join on course ID it shows all assignments but not student status
If I make a join on courseID and student ID it ONLY shows assignments that students have progress on.
I need to show all assignments, even if students have no progress, and the teachers need to be able to add a status (started, completed).
One option is I could generate statuses in my join table every time an assignment is added or removed or a student is added or removed, but that's a lot of junk data with a lot of coding and many instances... I'm wondering if there's a cleaner way to do that where an empty join table is the same as a status "0".
That's why I wrote
and
That should give you a record, regardless if the student progressed or not, when selecting a student. The "other information" bit in the last quote can include the status information you mentioned the teachers need to add.
OK thanks...
I was wondering if that was necessary and felt like if maybe I just joined it differently that extra step of creating and deleting records every time a student or assignment is added or deleted could be avoided.
I guess the way around that would be to use SQL to check student status and just create a routine to change that... I'll kick around those two options and see which makes more sense.
Thanks again!
@bdbd's suggestions are very good.
If you want an alternate way to enter the data you can use a pattern that I often use for long lists that have multi-faceted data.
There is a bit of setup, but it's fairly simple, and you get good results.
Use the list of course assignments as the user interface. Have a global field to store a single student ID. Display fields from "Student_Assignment" join table on the layout, to allow editing of student related data for each assignment. The relationship may or may not allow creation of records, depending on your needs.
I think @bdbd has it: you need to create an empty blank record to show "this assignment has been created and assigned to the student, but nothing has happened yet"
I've written these sorts of databases as well. Some general issues I've run into:
Here's a super tricky one I ran into in real life:
TLDR: The real world is messy!
OK Thanks all. I was feeling like I was missing something, but it looks like I have to generate the records.
@Malcolm this happens on the attendance screen which for various reasons has to be the class list (students enrolled in the class)
@xochi those are the sorts of issues that will come up which is why I was trying to avoid making a whole bunch of assignments that may or may not be used. We're an alternative school so only about 25-30% of our students actually do all the assignments, even if they're all assigned to them. In addition the teachers adjust courses midway through. Removing and adding classes. Adding assignments is a little cumbersome, but removing is nerve-wracking. It was just feeling like a lot of specific code that has to be built and tests so figured I'd reach out to the Borg mind to see if there was something easier. Appreciate you taking the time!
Without trying to sway you one way or the other (especially if you are settling into a concept that makes sense and appeals to you), I will mention that I've used the approach that @Malcolm has described numerous times over the years, and it works well.
I think that, for some, it can take a little bit of extra thought to wrap one's brain around it the first time it is employed. And, depending on the relationship between the context used to display the list and the main context of the layout, it can require a little extra TLC to make sure that all relationships refresh correctly when the global field is updated. If that is not understood or handled properly, it can feel a little mysterious in a disconcerting way, but once understood and properly handled, the reliability of the technique becomes clear.
If I've understood this thread accurately, I don't see any reason why the contexts required for this scenario would prohibit using this technique. But again, I'm not hoping or intending you to sway you towards such an approach. Mostly I just wanted to weigh in as another dev who has used it successfully over the years.
Hope this helps, and doesn't muddy the waters for you.
@Malcolm Ok yeah I think I'm starting to grock how it could work. Let me look into it a little more and play around.
Thank you! Got it working. Not sure if this is exactly what you meant, but I think it is, and it works like a charm without having to regenerate all sorts of data. Still in ugly mode, but it works!
And thanks @steve_ssh for pushing a little. I know you weren't trying to "sway" me, but this is awesome and exactly what I wanted...
That’s it. You are using the global as a transient key in the relationship.
I clear the key after use.
The drawback of this method is that global key restricts you to viewing one student record. You can’t open several windows and compare student activity. For this reason it should only be used in situations that are dynamic. You would not use it on the student’s main data form, as you could have two windows open for different students and data for one or the other will be wrong.
that make sense. Thanks for the warning. Shouldn't matter on the attendance screen.
Really happy to see this, @JasonMark . Kudos to you for working it out. It's a nice technique to have in one's pocket.
