1 Reply Latest reply on Jan 16, 2009 12:51 PM by TSGal

    relational help needed



      relational help needed


      I've got a relational database type question that I hope you all can help with.


      Situation:  I've got a gradebook database set up. I've got several tables involved and I need help organizing it.

      students:  a table with student info ( stu_id , fname , surname , fullname ) 

      students:  a table with student info (stu_id , fname , surname , fullname) 
      teachers:  a table with student info ( staff_id , fname , surname , fullname )
      notes:  a table in with a few fields ( stu_id , staff_id , notes )
      course: a table that has ( staff_id , course_id , name , desc , dept )
      enrollment:  a table that has ( stu_id , course_id )


      So, the idea is that a teacher can add notes about a student. Obviously there would be a variety of notes made on a particular student by various staff. Likewise a teacher may make several notes on one or more students. 


      I've set up a "enrollment" table to allow many to many relations between students & staff. This should allow for many students to take one course and students to take various courses. Did i do that right?


      next, Should I do the same thing for the "notes" so that I can have a many to many relationship for the "notes"?


      Then, because there will be several students, How does one ensure that when entering data the correct stu_id is linked to the student AND/OR staff_id is matched correctly?


      is this making sense? I think I'm on the right track, but I may have gotten a bit confused in the process. Any help would be much appreciated.


      OH, I posted earlier about "look ups with a drop down option" and i'd like to include that info in the database.


      thanks again




        • 1. Re: relational help needed



          Thank you for your post.


          It sounds like you have everything set up fine.  However, you may not want to do that with Notes.  That is, you may have many notes per each student, but you won't have many students for each note.  True?  Therefore, it would be a one-to-many relationship.  Therefore, each Note would have a staff_id (for the teacher writing the note) and a stu_id (associated with the student).  Mostlikely, the teacher will be looking at a student record, and then want to attach a note.


          In your Note table, you could select the teacher and the student before writing the note.


          I'm not sure what the process is, but this may give you a couple of ideas.



          FileMaker, Inc.