Re: Ordered Hierarchies.

Have a look at recursive CTEs before you go the proposed route. There are
plenty of good examples out there. The old SQL Server documentation had
some excellent examples with will also apply to PG.
One of the potential issues with recursive queries / CTEs is performance.
In one SQL Server-based system we were heavily dependent on recursive views
that performed more than adequately. You will need to be careful with
design, and also limit the hierarchy depth for optional performance.
This approach is often used in manufacturing bill-of-material structures,
for example.

Also, not sure about Postgres, but in SQL Server there used to be a limit
to the depth of the recursion, I think 22 levels or something like that.

On Thu, 18 Jul 2019 at 04:46, 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.
>
>
>