Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004

    Exclamation Database planning - Hierarchical problem - Help needed...


    I'm actually on a problem i don't manage to resolve.
    I want to implement a thing like this in a table :
                  Albert           John
                /        \          /
              /            \       /
        Bert                Chuck
                            /    |   \
                          /      |     \
                        /        |       \
                      /          |         \
                 Donna        Eddie       Fred
                                  \        /
                                   \      /
                                    \    /

    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)

    Thnks in advance,

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    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).


  3. #3
    Join Date
    Mar 2003
    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.

  4. #4
    Join Date
    Mar 2003
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts