Design question about tables

[categ]

Hi, I have been trying to learn database design for quite a while now. I
never seem to have a block of time to devote to it though. I am back at it
after a long hiatus. One question I have never seen addressed is:

Why in RELATIONSHIPS is there sometimes a second table listed? That is, if
you have an EMPLOYEES table, why is there an EMPLOYEES1 table in the
relationship grid? An example can be found in the CALL TRACKER template in
Access.

I haven't been able to figure out the logic and I am wondering if it is a
missing link in my design efforts.

Thanks in advance for any help offered.
Cate

 

[Allen Browne]

You need a 2nd copy of a table in the Relationships window if you want to
create 2 relationships between a pair of tables, if a self-join.

Typical example: table of employees has an EmployeeID field (primary key),
and a SupervisorID field. The supervisor is an employee, so that field needs
to relate to the EmployeeID field of another record in the same table. So,
you add a 2nd copy of the Employees table, and create a relationship from
Employees_1.SupervisorID to Employees.EmployeeID.

Another example of self joins:
http://allenbrowne.com/ser-06.html