Clever Geek Handbook

Help with a many-to-many relationship

I have a problem with a many-to-many relationship in my spreadsheets that is between a staff member and an instructor who work at the training center. I cannot find a connection between them and I don't know how to get it. Employee fields:

  • employee no.
  • employee name
  • Company name
  • Job department position
  • business area
  • Telephone number
  • ext
  • rank

Instructor fields

  • instructor name
  • Institute
  • Telephone number
  • E-mail address
  • pay
0


source to share


3 answers


in a many-to-many relationship, the relationship will be in the third table, something like

table EmployeeInstructor 
    EmployeeID
    InstructorID


to find all employees for a particular instructor, you must use a join for all three tables.

+7


source


Or more likely classes will be involved -

Staff member accepts class Instructor teaches class

so you will have EmployeeClass
table, InstructorClass table,

and join them. And the class must be unique, otherwise you will need



The class is learned in Quarter on ClassSchedule

and attach EmplyeeClassSchedule to InstructorClassSchedule.

It ends up becoming one of your more interesting relational projects pretty quickly. If you google for "Terry Halpin" and "Object Role Modeling" this is used as an illustrative situation in the tutorial.

+5


source


First of all, you will need a unique key in both tables. The employee number might work for the employee table, but you will need a different one for the instructor table. Personally, I tend to use auto-incrementing id fields with id in my tables. This is the primary key. Second, create a new table, InstructorEmployee. This table has two columns, InstructorID and EmployeeID. Both fields must be indexed. You can now create a link between any Employee and any instructor by creating a record containing two IDs.

+4


source






More articles:


All Articles