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?
