Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2009
    Posts
    4

    Question Unanswered: Hierarchy Database

    Hello!

    I want to place an Hierarchy Database for company employees, with unlimited sub-levels.

    This is my Level Table:

    ID Level Name Parent Level
    1 John Null
    2 Mary Null
    3 Ted 1
    4 Nick 1
    5 Nisa 2
    6 Tuck 5
    7 Smith 4
    8 Kary 3


    Now, I have the following chalanges:

    I want to prevent circular reference, that is to prevent that someone inadvertedly places John, boss of Ted, Boss of Kary, Boss of John again.

    I thought of doing this by editing "Parent Level" field with a Combo Box:

    The first level is easy, I just create a Query that looks for all records with field "Parent Level" Null. Now, I wish that if someone clicks on John, it has the option to choose John or to choose its subordinates Ted and Nick. For that, I would need a second Query that looks for all records with "Parent Level" John or 1.

    But how do I make the form in to manage this behaviour?

    If I could create a form with any sort of "tree view" to choose the boss, that would be even greater.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This isn't trivial.
    In case you didn't know, you are using the Adjacency-List model for your hierarchy.

    For unlimited levels, you need to use some recursive procedural code. It would be useful if you could define a realistic level above which you would never have to model (for example, the organisation never has more than 6 levels of management). In that case you could use a static query.
    To model your first constraint, you would need to traverse the hierarchy upwards to check that John does not feature above Ted before committing to the database. Again, this would need to be procedural code (it cannot be handled declaratively). It might be easier to use the Materialised Paths modelling technique to satisfy this requirement though.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    this illustrates my first point:
    Categories and Subcategories
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Dec 2009
    Posts
    4
    Hello!

    I guess I could set up a maximum number of sub-levels.

    Where can I find more about the Materialised Paths method or any other you would advise me (an easier one)?

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I've posted a link you probably haven't seen yet (I added another post). I would google Materialised Paths and come back with any questions.
    The gist is that it is the same as the adjacency model, except that there is an additional column that sores the hierarchy upwards for the person, usually as period separated notation.
    E.g. for Kary there would be a column containing:
    Code:
    3.1
    Obviously, this helps selecting data but makes changes to the hierarchy more difficult. As such, it is only really suited to hierarchies that are largely static.
    You may come across nested set hierarchies in your investigations - I would recommend you ignore these.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Tags for this Thread

Posting Permissions

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