Hi all.
I have read articles about handling hierarchies in databases but none of them deal with how to keep order in the hierarchy. For example take a typical outline.
1
1.1
1.1.1
1.1.2
1.2
2
etc.
In this scenario the following actions are common.
1. move the item up. 1.1.2 becomes 1.1.1 and 1.1.1 becomes 1.1.2
2 Move the item down. The opposite of above.
3. Move the item left. 1.1.2 becomes 1.2 and 1.2 becomes 1.3 and on down the 1.X list.
4. Move the item right. 1.2. becomes 1.1.3
5. Arbitrarily move an item into a hierarchy. In this case the item becomes the highest numbered child under the target parent and all it's previous peers get renumbered.
6. Arbitrary insert item into a hierarcy. It becomes the highest numbered child in the target parent.
7. Delete an item. This would renumber all peers in the parent greater it's own rank.
In addition there are all the normal access patterns of course.
Has anybody ever done anything like this or read an article about doing something like this in an efficient way?
I should also add that there are lots of more complicated actions one could take based on attributes of the nodes such as inheriting from the parent nodes some attributes or checking constraints based on parentage etc.