AnsweredAssumed Answered

relational help needed

Question asked by sully on Jan 12, 2009
Latest reply on Jan 16, 2009 by TSGal


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