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.