2 Replies Latest reply on Jun 29, 2010 9:00 AM by philmodjunk

    help with database design



      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.


        • 1. Re: help with database design


          I think you need to take small steps.

          1 I think each student should have a unique 'student code' not the class code.

          2. I think students who have left should be omitted not deleted

          Better than 2 there should be another linked look up data base/table that allows the first table to create a new record by student code but does not delete the record when the student is deleted on the first table.  Once you have been able to retain the student record safely in the second table then you can play with various viewing layouts


          • 2. Re: help with database design

            Expanding on Dumiya's item 2: Isn't it possible to have a student leave and then return in some point in the future?


            If you aren't using a portal, you may want to look up this term in filemaker help. It's a useful tool for displaying and editing multiple related records. WIth this on a students layout, you can open up a window to a list of related report records--one for each year the student was enrolled.