This appears to be a contradictory statement:
also a mentor can have multiple students, but only one student can be assigned to a mentor.
Do you mean that a student can only have one mentor?
If so, then you do not need Mentor_Student to serve as a join table between Mentors and Students as you would then have a one to many relationshiop between Mentor and Students, not the Many to Many relationship that requires a join table.
thank you for responding, yes I need to have the student and mentors to have a many to one relationship but I also need to have the capability to reassign students to different mentors... thinking about it right now after seeing this post I would just have to keep that information in the sessions table instead... to trace the students history and which mentor/ teacher they have/ had... if this is the case I would use the session table as the many-to-many relationship.... the mentor_student table is a table to hold the info to select the mentor then add multi ple students to that mentor, I'm try ing to use that table as the basis for the layout to make the pop up window over iwp...
Then this is not an accurate description of your relationships:
...but only one student can be assigned to a mentor.
In fact, many students may be assigned to a mentor and, from what I understand, a student can have more than one mentor.
I needed clarification on that before proceeding with any suggestions that I might make.
Won't there be many records in Sessions for a given pairing of Mentor to Student? (Won't a "Session" be one meeting out of many for a given mentor and assigned student?)
If so, I would not put that data in Sessions and I'd link Sessions directly to Mentor_Student via a Primary Key field defined in Mentor_Student.
Ok, as it stands now many sessions would pair mentors and students to track what they did in that session, so yes. I do like linking the mentor student table to the session table. so the session table can only hold the session notes and type of session.
now about the mentor and student situation, let me be clear in that one mentor can have many student this is assured. i guess it would be better to say that many unique students can be assigned to one unique mentor. i am trying to make sure that unique students get assigned to a mentor.
to design the database. i would like to have the many-to-many relationship for two reasons: first to validate that each student is not assigned to the same mentor (like a one to many wouldnt allow) so the student can be reassigned to a mentor and the date and time of change is recored, second (the main reason) to use as my popup window to assign those students to any of the mentors.
the many- to many is harder since the many to many doesn't care about unique values concerning multiple entries, like choosing the same student over and over to enter in the assigned student of the mentor. the many to many relationship would be to "move" a student to the selected mentors caseload (assigned student) i.e. my popup window since IWP is needed
so my screen shot is staring with that aspect and i thought that i could take advantage to the E-R method of designing the database. or else i would have the mentor table linked to the student table then add the student from the student table in the mentor table. or have a student detail(holding the mentor the student is assigned) and the mentor detail(holding the mentors students).
i hope this clears up my intensions thanks
i am trying to make sure that unique students get assigned to a mentor.
Which means that you don't want to assign the same student to the same mentor more than once.
You definitely need a many to many relationship here, it's a matter of correctly managing the details of that to avoid possible user errors.
See this demo file, which both illustrates a many to many relationship and also several different ways to prevent a user from selecting the same two records to link as you have described here.
Thank you Phil, greatly appreciated i'll look through all the relationships and ask you more questions from there... thank you for simplfing the scripts the apple basic coding is something to get used too... i'll probally will ask you why you did what you did as well...