Fwd: Ordered Hierarchies.

Essentially you will be doing a parent-child recursive lookup. At a basic
level, you table would look have three columns: [ID], [Name],[ParentID].
From there, you will need to perform a recursive lookup. This isn't a
PostgreSQL Example, but should give you the theory and idea:
https://www.codeproject.com/Articles/818694/SQL-Queries-to-Manage-Hierarchical-or-Parent-child.
I assume that if the ParentID is 0 or null, then it is at the root level.
If it's not going to be sorted by the name (alpha), you may need to add a
'Sort' column, too.

I've done something similar a menu system, but have always limited the
number of recursions. The only thing you have to watch out for is
orphans.

On Wed, Jul 17, 2019 at 9:46 PM Tim Uckun <timuckun(at)gmail(dot)com> wrote:

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