I'm building a database collecting data from 29 schools. Each school must send its student roster at the end of every year to the ministry of ed, along with the students grades, whether they passed or failed, and who their teacher was.

So far so good. A table for schools, teachers, students. But then I discovered that many schools have the previous years roster too (from '07-'08), and now I'm trying to build a more complex relational database.

I think I'm in over my head, but I'd rather here the bad news now before it gets full of records (right now I've only got around 600 kids and 11 teachers).

I would very much appreciate someone poking around my relationship structure to see the best way of organizing this.

Right now, I've got School, teacher, and parent tables. Then I've got school_07_08; teacher_07_08; and student_07_08 tables. Repeat for '08-'09 and '09-'10.

I want basic student and teacher tables, so I only have to type in their data once, then send that data out to the relevant year. (ie, enter in a student once into student table, then send it to the student_07_08 table and the student_08_09 table, with the relevant fields (name, gender) staying the same, and the time-based fields (grade, test score) changing.

All of a sudden I went from something somewhat straightforward to something spiraling out of control! Help!

Year_by_Year_Experiment_Clone.zip

I believe you should have three tables (for the current purpose): Schools, Students and Grades. The Students table holds the permanent info of a student (Name, Gender, etc.). The Grades table holds:

StudentID

SchoolID

Year

Grade

That definitely solves the multiple school years/grades for each student problem...but what about multiple years for each teacher problem? Teachers shift around in a school from year to year, and I want to be able to capture their effectiveness (measured by student scores). Any ideas? Thanks for the quick feedback!

You could add the TeacherID to the Grades table to link the teacher to the student, their grade and the school for that year.

The link between teacher, school and year will be repeated for each student, so maybe it would be better to make a new 'join' table to link TeacherID, SchoolID, Year.

The Grades table would hold the studentID and grade and link to school/ teacher/ year via this join table.

Forgive my newbie ignorance, but could you walk me through the process of creating a joining table?

It is nothing special - just another table. The Grades table is effectively a join table.

Make a new table and call it "Class" or "Appointment" or whatever...

It should have the following fields:

ID

TeacherID

SchoolID

Year

now it's a join table that records where the teacher was appointed that year i.e. links the teacher with the school for the year.

You need the ID field (autogenerated serial number) so that when you create a record in the Grades Table you can create a link to the "Appointment" table:

StudentID

Grade

AppointmentID (which links this grade to school/teacher/year)