I have an Offices table and a Departments table. I want to designate records in the Departments table as either "co-located" or "partner" with records in the Offices table. I will have to make at least one junction table, and I am wondering if I have done this correctly.



Right now I have the Offices table, the Departments table, and two junction tables in between, one for storing records of co-located departments and one for storing records of partner departments. I have set up the foreign/primary office/department keys properly, I think.

tblOffices - tblPartnerDepartments - tblDepartments

and

tblOffices - tblColocatedDepartments - tblDepartments

Now, I think I may have done this right. But I was wondering - might it be better to include records of partner and colocated departments all in one junction table, and then have another table linked to the junction table containing DepartmentType (colocated or partner) records? Or is there a third way of doing this? Or - is the way I have done it so far going in the right direction, but with some errors?

As always, I really appreciated any and all help.

canfish