Hi all,

Is there a Best Practice out there for implementing a sequencing hierarchy? This would be very similar to a windows explorer.

Currently, my implementation consists of two fields: SEQUENCE c(80) and SERIES c(1). I have an index on SEQUENCE+SERIES so that the grid would display the records in a hierarchical fashion. An example would be:

ID  PID  SEQUENCE  SERIES
 1    0  01000000
 2    1  01010000
 5    2  01010100
 6    2  01010200
 3    1  01020000
 4    3  01020100
 7    4  01020101
...

My problem is when I assign ID 4 to have a PID of 1 and move it after ID 2, I would have to change the sequence of ID 4 to that of ID 3 or 01020000. Then recreate the sequence of ID 3 and its children. Now imagine this if you have thousands of PID=1 records. I will have to update every sequence after the insertion point recursively down to its children.

So I was wondering if there is a better approach to my problem. Feedbacks most welcome.