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.
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.
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:
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.