so I am recreating a database for my district, 3 schools. We are deploying this from one school (of which I've written about a lot) to three schools.
One of the features the principals would like is to be able to do teacher evualtions in here. THis allows the database to keep track of the scores and for teachers to see their own information.
The evaluation is a bit complex:
Each teacher is evaluated in up to 29 "Performance Indicators". These indicators are grouped into competencies. The principals want to be able to start a new evaluation by seeing the teacher's name at the top of the screen with all the indicators below, grouped. Each indicator also contains four text boxes that need to be filled in.
It seems a way to handle this is to have a table called "Evaluations" with the basic evaluation info (date, person observing, teacher ID, observation ID, etc) and a "Performance Indicators" table (similar to an Invoice / Line Items table structure) When clicked on a button, the principal creates a new record in the Evaluations table (related to the teacher table). This in turn creates 29 new records in a "Performance Indicators" table with the correct Evaluation ID (from Evaluations table), so these indicators can be seen a portal where the information is filled in.
The issue comes when I have to calcuate the maximum of a teacher's score over multiple observations FOR EACH indicator. I can do this, but it seems I'm going to have 29 TOs of the "Indicators", one for each indicator, so that the Evaluations table can make the calculations of just one indicator at a time. So my evaluations table would have 29 different calc fields, each one finding the Maximum score of the related records in the Performance Indicator, (related of course by the indicator number).
I worry that the 29 TOs should be handled better, but the more i think, and the longer I type this post, it seems to be the only way to do this.
Am I right?
Is there a better way to do this?
The attached pic shows the evaluation information that needs to be filled out per observation. It doesn't show all 29 indicators, just a few of them. The 2nd pic shows my first attempt at this - an Evaluation layout with a portal containing all the indicators related to that observation.