I'm developing a database to allow tutors and staff to record individual assment marks and comments per subject as this functionality isn't in a larger system. I've managed to setup a basic test case of how this will work, but I am stonewalling on some simple relationship issues.
I want to be able to view a student either directly or through another table, and add a Grade::mark to each assessment they complete. Each student will complete multiple assessments per each subject they attend, but each assessment they complete will only have 1 Grade::mark recorded. In my current setup this sort of works, but through portals I have to add each assessment by first adding a grade then selecting the assessment from a drop-down. As I'm developing this for others I would like to stream-line the whole approach so that there is a list of all the assessments already populated for a given subject for a given student that a tutor can fill in easily. I would prefer this to be a portal but it doesn't need to be as long as the process is easy. A student can only complete a subject once. If they repeat a subject it will be a new occurance of the subject and tutorials. Assessments are also liable to change each semester.
I have a feeling this can be solved by adding another series of table occurences where the tutors enter the grade::mark results, but I have tried several ideas and they haven't seemed to work.
This is a mockup of the database just to test my relationships, I understand its basic look and minimalistic fields.
Any help with this would be greatly appreciated.