Обсуждение: Resources on modeling ordered hierachies?

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.

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

Items could of course be "promoted"  this way or moved up and down the
hierarchy to be below or above their peers.

Does anybody know of a more elegant way to do this?

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@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

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