Hi,

I'm actually on a problem i don't manage to resolve.
I want to implement a thing like this in a table :

All these links are work relationship between employees...
So for example, Bert superior is Albert.
And Chuck has two superiors : Albert and John

What's in ur point the best method to organize this in a table and do simple queries on it ?

I tried a modified preorder tree traversal algorithm but since it's not hierarchical anymore, i don't manage to get it to work... (some item have two superiors)

I'm using MySQL 4.1 and ASP

Any help or clue welcome... (here or by direct email contact)
philippe.lecomte@step.fr

Thnks in advance,
Philippe

I'd use two separate tables. One table tracks information about a specific employee, a separate table tracks the relationships between employees (it has two foreign keys, one FK to the employee and a second FK to the employee's supervisor).

-PatP

I think that is very much like a typical Bill of Materials (BOM) that is very common in manufacturing systems. A BOM has assemblies and components and sub-components; you know what I mean? If I am correct that what you need is the same in the basic design, then you can get a lot of help by looking for BOM software/databases.

Also, if this is a typical BOM type of application, then there is a problem that often needs to be solved in applications such as this. It is easy to make a certain mistake and it is usually difficult for software to catch the mistake. I don't know how to explain it except by example. It would be a mistake to make Gilbert "superior" to Chuck, but that would create a circular relationship that might cause your software to loop infinitely. For your application, though, it is not likely to happen and it should be easy for people to "see" the problem, but it is a potential problem.