help with database design
I'm a beginner with FM9v3.
I've not got much experience with database design and my training dates from years ago, so I could do with a bit of advice!
Here what 've got:
A Student file with all students of the current year. To a student is affected a class. At the start of the following class year, all class id are changed., the students who have left are deleted from the database.
I've been asked to design a solution which allows teachers to keep records of the students' Reports over the years. The teachers need a screen where they can enter a report, as well as where they can view the child current and previous years reports.
What I have tried:
I've created a Report file with what I see is a key made of Student_Id and School_year_id. I've created a relationship between the Student table and the Report page, where Student_Id in Student is equal to Student_Id in report (but not mentioned the School_year-Id at that level). I've allowed creation of records in Report via the relationship.
I've created a layout for Report Entry. I've made it show the records from Students because the teachers (when they enter reports) like to do a Find on their class and then enter the report for each child in turn. On this layout, I'm showing the related record in Report. One of the fields is School_Year_Id with a drop-down list of School years.
Using this layout, I've been able to create a record in Report for the current year. But when I try to create a record for another year, it doesn't work of course as it only changes the field School_Year to the new value, but doesn't create a new record for that year.
Obviously, there is a major design flaw somewhere but I can't figure it out. Thank you for your help.