Re: Resources on modeling ordered hierachies?

On 2022-04-07 17:45:49 +1200, Tim Uckun wrote:
> There a tons of articles about how to model hierarchies in SQL but I
> haven't seen any about dealing with hierarchies where the order of
> children is important.
>
> The canonical example is a simple outline
>
> 1.
> 1.1
> 1.1.1
> 1.2
> 2.
> 2.1
>
> etc
>
> If I am doing an insert where parent is 1.1 it should name it 1.1.2
> which to me means doing something like select max(id) + 1 where parent
> = 1.1 or something like that which might turn out to be expensive.

Shouldn't be that bad with an appropriate index. Bigger problem might be
that two transactions could attempt this at the same time.

> Similarly if I want to insert something between 1.1 and 1.2 I need to
> do something like update id set id = id+1 where parent = 1 and id >1

You can mostly get around that by using float8 or even numeric instead
of int. Chances are that there is a free number between you numbers.

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"