proper database desing

[Lee Johnson]

I have 2 tables EMPLOYEE and EMPLOYEE_TYPE and one table called JOB. In JOB
I need to store employee_id in two fields (pilot_id and engineer_id) based
on employee type.

What is the best way to structure this?

Thanks,

Lee

 

[Allen Browne]

Drag two copies of the Employee table into the Relationships window (Tools
menu).

Access will alias the second copy as Employee_1.

Drag Employee.EmployeeID onto Job.Pilot_id to create one relationship.

Drag Employee_1.EmployeeID ont Job.Engineer_id to create the other one.

 

[Lee Johnson]

Ok, I understand that. I'm developing in an ER app.but I'm sure I can
figure it out. What I was more interested in was the ER theory behind it.
Is this a self-join table in essense?

Thanks,

Lee

 

[Allen Browne]

Not really a self-join; you have two different joins from Employee to Job.

A self-join would be where a table looks itself up.
For example, if the Employee table has a SupervisorID field, which contains
the EmployeeID of the person's supervisor.