Help with many-to-many relation

Question

I have a problem with a many-to-many relation in my tables, which is between an employee and instructor who work in a training centre. I cannot find the link between them, and I don't know how to get it. The employee fields are:

• employee no.
• employee name
• company name
• department job title
• business area
• mobile number
• ext
• ranking

The Instructors fields are

• instructor name
• institute
• mobile number
• email address
• fees

Answer 1

in a many-to-many relationship the relationships will be in a 3rd table, something like

table EmployeeInstructor 
    EmployeeID
    InstructorID

to find all the employees for a specific instructor, you'd use a join against all three tables.

Answer 2

First of all, you will need a unique key in both tables. The employee number may work for the employee table, but you will need another for the instructor table. Personally, I tend to use auto incrementing identity fields called 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 should be indexed. Now you can create an association between any Employee and any Instructor by creating a record which contains the two IDs.

Answer 3

Or more likely there will be classes involved --

Employee takes Class Instructor teaches Class

so you'll have and EmployeeClass table,
an InstructorClass table,

and join through them. And Class needs to be unique, or else you'll need

Class is taught in Quarter on ClassSchedule

and end up joining EmplyeeClassSchedule to InstructorClassSchedule.

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