I've decided to post a lot of questions on here. Here's one.
At my school we are using a database to store information from previous years. Each kid for the current year has a schedule and a grade. I'm a 5th grade teacher, so all my kids have the grade 5 label attached. Next year, I'll move the kids to 6th grade, and so the 5 will be replaced with a 6.
As I said, the database stores info from the past. It stores last year's grades, when the kids were 4th graders. All of the Academic data are in appropriate tables and those tables are related to the student table by the student ID and year, so I can have many years worth of one student's grades in one table. (I think this is the proper way to store information).
My problem is that when I print a report card from LAST year, when my kids were 4th graders, their report card says 5th grade (current year). I don't want that. I want the grade that the kid was in when he/she earned those grades.
So what should I do? Currently the grade field is in the student table. But now that I'm storing multiple years worth of data, it doesn't make sense, i think to keep the grade field in the student table where it is changed every year.
Should I create a new "StudentGrade" table with three fields: __pk_StudentID ; AcademicYear; GradeLevel
Or should I create a new field in the academic info table and record the grade level that they earned the particular score? Is one better? Is one more efficient?
Just a note, I do have multiple academic info tables (State Test scores, local benchmark test scores, etc) storing multiple year's with of data for each kid.
I'm thinking i should create a new table and put in the kid's academic year and grade level, as my first option shows.
Am I thinking correct?