Alternative to using a sort order column in a database table

Question

Similar to this question, however none of those answers look ideal.

Consider this Django model used with PostgreSQL:

class Parent(models.Model):
    id = models.AutoField(primary_key=True)

class Child(models.Model):
    id = models.AutoField(primary_key=True)
    parent = models.ForeignKey(Parent)

In SQL (hope I got this code right):

CREATE TABLE parent (
    id integer PRIMARY KEY
);

CREATE TABLE child (
    id integer PRIMARY KEY
    parent_id integer REFERENCES parent
);

How can it be made so that Child objects belonging to a specific Parent can be reordered? i.e. using a drag drop UI, user can drag Children around and save the updated ordering. Creating, updating, and deleting a Child should only require one operation, i.e. shouldn't require updating something on Parent.

I have some imperfect ideas with reasons why each idea is not ideal:

1. Sort Child alphabetically with a varchar provided by the user - This forces the user to think of a name starting with a certain letter or number, requiring a stressful amount of creativity for no justifiable reason.

2. In Parent, store an array of integers that correspond to the primary key of Child objects - Requires more than one operation for creates and deletes, the array in Parent would need to be updated each time. No database enforced data integrity.

3. Sort Child by an integer column - Requires an additional read when creating Child to find the next integer to use. Another problem (regardless of how unlikely it is to happen in production) is that when the gap between integers runs out, the objects "behind" the inserted object must be pushed back. This situation can be artificially forced by repeatedly taking the last object and putting it first.

4. In each Child, store the integer id referencing the next/previous Child - This solves some of the hacky problems from 3., but it seems absurdly inefficient, and a logistical challenge.

Are there any eloquent solutions to this requirement (that don't involve an addition join or replacing the second table with a jsonb)?

Answer 1

How about using a float as sort-order column. When you insert/move between to items, set the sortorder to (previus-item.sortorter + next-item.sortorder) / 2. This will not run infinitly, but depending on the system you should not run into realworld problems. Also, you could have a service-task that resets the gaps in the sortorder of your childs over night.