Ordered Hierarchies.

From: Tim Uckun <timuckun(at)gmail(dot)com>
To: pgsql-sql(at)lists(dot)postgresql(dot)org
Subject: Ordered Hierarchies.
Date: 2019-07-18 02:46:12
Message-ID: CAGuHJrMQKA5drnW=+1VRpWwJbXa9UFAu6Cc+ACkkHE1LxMpTHg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tumasgiu Rossini 2019-07-18 08:43:03 Re: IN vs arrays (was: Re: how to resolve org.postgresql.util.PSQLException: ERROR: operator does not exist: text = integer?)
Previous Message Andrew Gierth 2019-07-16 16:01:39 IN vs arrays (was: Re: how to resolve org.postgresql.util.PSQLException: ERROR: operator does not exist: text = integer?)