Using Real numbers for explicit sorting in sql database

Question

i'm facing a recurring problem. I've to let a user reorder some list that is stored in a database.

The fist straightforward approach i can think is to have a "position" column with the ordering saved as a integer. p.e.

Data, Order
A     1
B     2
C     3
D     4

Problem here is that if i have to insert FOO in position 2, now my table become

Data, Order
A     1
FOO   2
B     3
C     4
D     5

So to insert a new line, i have to do one CREATE and three UPDATE on a table of five elements.

So my new idea is using Real numbers instead of integers, my new table become

Data, Order
A     1.0
B     2.0
C     3.0
D     4.0

If i want to insert a element FOO after A, this become

Data, Order
A     1.0
FOO   1.5
B     2.0
C     3.0
D     4.0

With only one SQL query executed.

This would work fine with theoretical Real Numbers, but floating point numbers have a limited precision and i wondering how feasible this is and whether and how can i optimize it to avoid exceeding double precision with a reasonable number of modifications

edit:

this is how i implemented it now in python

@classmethod
def get_middle_priority(cls, p, n):
    p = Decimal(str(p))
    n = Decimal(str(n))
    m = p + ((n - p)/2)

    i = 0
    while True:
        m1 = round(m, i)
        if m1 > p and m1 < n:
            return m1
        else:
            i += 1

@classmethod
def create(cls, data, user):
    prev = data.get('prev')

    if prev is None or len(prev)<1:
        first = cls.list().first()

        if first is None:
            priority = 1.0
        else:
            priority = first.priority - 1.0
    else:
        prev = cls.list().filter(Rotator.codice==prev).first()
        next = cls.list().filter(Rotator.priority>prev.priority).first()

        if next is None:
            priority = prev.priority + 1.0
        else:
            priority = cls.get_middle_priority(prev.priority, next.priority)

    r = cls(data.get('codice'),
        priority)

    DBSession.add(r)

    return r

Answer 1

If you want to control the position and there is no ORDER BY solution then a rather simple and robust approach is to point to the next or to the previous. Updates/inserts/deletes (other than the first and last) will require 3 operations.

Insert the new Item
Update the Item Prior the New Item
Update the Item After the New Item

After you have that established you can use a CTE (with a UNION ALL) to create a sorted list that will never have a limit.

I have seen rather large implementations of this that were done via Triggers to keep the list in perfect form. I however am not a fan of triggers and would just put the logic for the entire operation in a stored procedure.