Unanswered: Help! Stuck on School Reporting Database design!
I'm trying to develop a database to help streamline a school reporting process, but am having trouble getting my head around the relationships diagram, as well as then 'converting' that into a workable interface.
I'd be grateful if anybody could offer some advice about this project, as it needs to work well in order to be useful!
What I need is this:
(1) Enter very basic STUDENT info: StudentID (A/N), Name, Surname, Year Level. Nothing fancy.
(2) Enter SUBJECT info: SubjectID (A/N), ExtCode (external moderators code), SchoolCode, SubjectTitle (e.g. Home Economics), SubjectSubtitle (e.g. Food and Hospitality), SubjectYearLevel. There are often subtle differences between subject names, especially at senior year levels, so there might be very similar entries - e.g. Mathematical Studies versus Mathematics Studies. It's silly, but that's the way the education board does it! Generally this means that certain subject names are specific to certain year levels.
(3) Enter TEACHER info: TeacherID (A/N), TeacherCode (generally the first three letters of their surname - each is unique to the school system), Name and Surname.
(4) Create CLASSES (of course - many to many combination of STUDENTS, CLASSES, TEACHERS and SUBJECTS). There also needs to be a field that concatenates different values (subject Year Level, SchoolCode and TeacherCode) to create a unique value something like this: 11EngStSmi (for Year 11, English Studies, Sue Smith).
It's at this point that my head gets a bit tangled. I want an interface where a teacher (say, Sue Smith) selects her name from a drop down list. When she does that, she will see a list of all her current classes (e.g. 11EngStSmi, 11ModHisSmi, 11DraStSmi, etc). Beside each listed subject, there should be a button that, when clicked, opens a form applicable to each individual class and displays a list of all students in that class. Beside each student's name there should be space to enter reporting details, such as School Grade, ExtGrade, ExamPercentage, AppPart Grade, Teacher Comments, etc). The other complication is that certain subjects will have individual assessment components (e.g. EngSt might have Text Analysis, whereas Maths might have Data and Recording) that need an individual grading structure.
All this then gets merged into a report template to then be printed as final school reports. I have absolutely no idea whether the relationships diagram I've attached to this message is leading on the right track, nor am I able to figure out how to piece it all together into the forms interface I have described.
If somebody could please help me, it would be much appreciated!
Get your relationships in order before getting hung up on this form you are imaging.
You need an intersection table. it would basically be a primary key and a foreign key to each of your tables. It would probably also have the term and the year of the class. it may even have the time of day or maybe section, depending on how you want to handle it.
Try that and let us know how you are doing.