Imagine you have a Table Employees, with values ID (PK), Name,
Supervisor (FK). Supervisors are also employees, so 'Supervisor' is a
self-referencing foreign key to ID.

We realise that supervisors supervise more than one employee. Now
imagine that employees can have more than one supervisor - thus, a
many-to-many, self-referencing foreign key.

How can this be done?

(The example is for discussion's sake - in reality, my table is Tag ID,
Tag Name, and Parent, etc.)

Many thanks

isporter (AT) gmail (DOT) com wrote:
I guess this would work:

employees(id*,name)
employees_supervisors(employee_id*,supervisor_id*)

- supervisors are all those in the employees_supervisors whose
supervisor_id is NULL
- both employee_id & supervisor_id reference employees.id

<isporter (AT) gmail (DOT) com> wrote

The term I'm familiar with is "reflexive relationship" rather than "self
referencing key", but that's just a matter of terminology.

To deal with your question, the answer is the same as it would be for any
other kind of many to many relationship: you need another table, say table
Supervision with columns (EmployeeId (FK) and SupervisorId (FK)). The PK
of this table is the entire row.

EmployeeId references Employees.ID, and so does SupervisorID, but they
don't reference the same row. (unless an employee is his own supervisor).

The most common situation where a table like this is needed is parts
explosion. When parts are components of other parts, the relationship is
many-to-many.

HTH.

Thus, is the following correct? I have tags that are components of
other tags. Articles are tagged. Each article has a primary tag.
Articles are either content or a rule to select other articles based on
tags. tags have parents and a child rank. This way I can build a
navigation based on tags. I'm not sure if there is a better way to do
this.

http://www.intraspin.com/db.gif

Thanks again

David Cressey wrote:

with itself. You need to create an associative entity just
like any other many to many. The first relationship
identifies all of the supervised employees. The second
relationship identifies all of the supervising employees.
The is your standard network data structure.

strawberry wrote:
IMO both attributes in employees_supervisors should be "not null", and
together they are the p.k. of the table. A *proper* supervisor is those
emploees that exist as supervisors but not as employees in
employees_supervisors


/Lennart

Lennart wrote:
I'd go along with that.

"Lennart" <Erik.Lennart.Jonsson (AT) gmail (DOT) com> wrote

You are right.

In most companies, supervisors have supervisors, although they may have the
title "managers" or something like that.
The employee_supervisors table is capable of recording the structure of a
tree structured hierarchical supervision relationship all the way up to the
CEO.

David Cressey wrote:
Yes, I'm aware of that. I was just trying to mimic what I thought was
the OP's intention with
the NULL row in employees_supervisors.


/Lennart

Get a copy of TREES & HIERARCHIES IN SQL for several orther ways to
model this problem. In poarticular, look up "Nested Sets"