Re: Ordered Hierarchies. - Mailing list pgsql-sql

Hi Everybody. I have looked at the solutions proposed. Specifically I looked at recursive CTEs,  nested sets, materialized paths,  using arrays to represent parentage, and ltree.  They all presume an unordered set of children and in my case the order of the children is the most important thing. So far it's looking like I need to probably write some complicated stored procs to do what I want and I will definitely need a "rank" or some other column so can say something like "update table T set rank=rank+1 where parent_id=x" and then "insert into table T values (parent_id, rank, item_desc)" or something like that. In order words if I want to insert this item at 1.2..3 I need to increment all 1.2.X by one and then insert this.  I know, race conditions galore!!

On Fri, Jul 19, 2019 at 8:58 AM Dmitry Ruban <dmitry@ruban.biz> wrote:

Hi, 

There's an approach to store such hierarchy in relational db, have a look

https://en.m.wikipedia.org/wiki/Nested_set_model

I think it covers your usecases 

Cheers 

Sent from phone

On Thu, 18 Jul 2019, 14:46 Tim Uckun, <timuckun@gmail.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.