AnsweredAssumed Answered

Many to Many Relationship Setup

Question asked by ClayHendrix_1 on Feb 23, 2012
Latest reply on Feb 25, 2012 by ClayHendrix_1


Many to Many Relationship Setup


I have studied several sources regarding having this type of data in a database. I have learned a lot. One time, I had it working, but I messed it up and cannot recreate what was working.

Specifically, I am trying to create a database that tracks the professional development sessions teachers attend. The two main tables are TEACHERS and PDSESSIONS. The primary key fields are _kp_apscn_teacher_number and _kp_session_id, respectively. I created a third table called LINK and put two fields _kf_apscn_teacher_number and _kf_session_id as well as _kp_link_id (auto serialized). I have linked the primary key fields from the main tables to their respective fields in the LINK table with a one-to-many relationship with the one sides on the TEACHERS and PDSESSIONS tables and the many sides being on the LINK table. It's the pciture that you see all over the Internet for how to setup a many-to-many relationship between two tables using three tables and not actually having a many-to-many relationshop directly between two tables.

In my specific situation, multiple teachers attend almost every PD session and all teachers are required to attend more than one PD session. 

I have started over multiple times. I typically get different failings. Two common failings are that the relationship between TEACHERS and LINK changes from one to many to be many to many. Also, sometimes I cease being able to add records to PDSESSIONS using the form I made.

Ultimately, I want to use a portal to display all of the PD sessions a teacher has attended on the form displaying data from the Teachers table. I would also like to be able to add multiple teachers as having attended PD sessions from the PS session form. I know people do it all the time, but I can't seem to get it all correct.