sql join to different columns

I have a table that holds project manager and project director employee id's e.g

projectmanagerId
projectdirectorId

I need to link each of these columns to a employees table, however the problem is that the both the projectmanagerId column and ProjectDirectorId column need to link to either of 2 columns e.g

JEmployeeId
MEmployeeId

I thought about creating a junction table like so

ProjectManagerEmployeeJunction1
-------------------------------
projectmanagerId
JEmployeeId

ProjectManagerEmployeeJunction2
------------------------开发者_运维知识库-------
projectmanagerId
MEmployeeId

and similar for the director. Is this the best way to do this. Is there a better way. It seems overkill to create 4 tables for this.

I would approach it in a similar but slightly different way.

Rather than creating the table for ProjectManagers, and/or ProjectDirectors, create the table for all employees.

EmployeeIDJunction
------------------
externalID,
EmployeeUniqueID

An employee would then have two entries in the table. One record where the externalID is the JEployeeID, and one record where the externalID is the MEmployeeID. The EmployeeUnqiueID would then be any ID that you wanted to, that maps to your employee table. You could, in theory, have as many as you liked, provided they were unqiue across the whole table (So as to avoid mapping one externalID to multiple employees).

  Data
INNER JOIN
  EmployeeIDJunction
    ON EmployeeIDJunction.externalID = Data.EmployeeID
INNER JOIN
  Employee
    ON Employee.ID = EmployeeIDJunction.EmployeeUniqueID

Just to re-itterate. This all assumes that the different EmployeeIDs NEVER collide. I can't EVER have an ID in one system that YOU ALSO have in a different system.

If that's possible you need to start doing things like prefixing the IDs, or using composite keys such as (SystemId, EmployeeID) pairs. Either way, the concept above should still allow you to map multiple ID's to a single Employee record, without using OR's and so getting maximum benefit from INDEXes.

I think you can use INNER JOIN with OR clause, for example:

t1 inner join
t2 on projectmanagerId = JEmployeeID or projectmanagerID = MEmployeeID

• Get all your employee data into the new table
• Copy over everything that isn't in the new table
• Copy over the old employee ID too
• Update your project data records to replace old id with new id:

In pseudo sql:

update project_table set
employeeid = (select newid from newemp where old_id = employeeid)
where employeeid in (select old_id from newemp);

Now you only have one relationship to deal with.