On 4/7/22 10:55, Peter J. Holzer wrote:
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
Is there any chance the 1/1.1/1.1.1 stuff can be cosmetic/generated? Corollary: what determines the hierarchical position of the next record?
Each item could know its parent (nullable) and it next-sib (nullable). If, as your example might suggest, you are always adding to the end, then you're updating the last sib's null next-sib point and the incoming record has next-sib null. All the single numbers are parentless, 2 is next sib of 1. Trickier to interject a new record between to sibs but not impractical. Can move sibships up/down hierarchy by updating parent and resetting next-sib of last in moved sibship and the next-sib of the injection point to the head of the move sibs.
