Reordering rows in sql database - idea

Question

I was thinking about simple reordering rows in relational database's table. I would like to avoid method described here: How can I reorder rows in sql database

My simple idea was to use as ListOrder column of type double-precision 64-bit IEEE 754 floating point. At inserting a row between two existing rows we calculate listOrder value as average of these sibling elements.

Example:

1. Starting state:

value, listOrder
a       1
b       2
c       3
d       4
e       5
f       6

2. Moving "e" two rows up

One simple sql update on e-row: update mytable set listorder=2.5 where value='e'

value, listOrder
a       1
b       2
e       2.5
c       3
d       4
f       6

3. Moving "a" one position down

value, listOrder
b       2
a       2.25
e       2.5
c       3
d       4
f       6

I have a question. How many insertions can I perform (in the edge situation) to have properly ordered list.
For the 64 bit integer there is less than 64 insertions in the same place. Is floating point types allows to more insertions?

There are other problems with described approach? Do you see any patches/adjustments to make this idea safe and usable in applications?

Answer 1

This is similar to a lexical order, which can also be done with varchar columns:

A
B
C
D
E
F

becomes

A
B
BM
C
D
F

becomes

B
BF
BM
C
D
F

I prefer the two step process, where you update every row in the table after the one you move to be one larger. Sql is efficient about this, where updating the rows following a change is not as bad as it seems. You preserve something that's more human readable, the storage size for your ordinal value scales in a linear rather with your data size, and you don't risk coming to a point where you don't have enough precision to put an item in between two values