[gluxon]

There may be other reasons, but here's one. The solution with fractions supports ORDER BY syntax. With the self-referential foreign keys, you would have traverse through from the beginning with multiple queries for that behavior.

It's also a lot harder to query results in order while specifying an offset.

[pabl0rg]

Couldn't you just order by preceeded_by ? Offsets should work

[wainstead]

I think one of the first design choices you have to make is: can preceded_by be NULL? The first item in the list has to do something with preceded_by. As a general rule making columns NOT NULL is prefereable; here I declared it NOT NULL, and the first row must reference itself as a result:

  nworks=# create table todo (todo_id int primary key, task text, preceded_by int not null references todo (todo_id));
  CREATE TABLE
  nworks=# insert into todo values(1, 'do homework', 1);
  INSERT 0 1
  nworks=# insert into todo values(2, 'clean bedroom', 1);
  INSERT 0 1
  nworks=# insert into todo values(3, 'walk the dog', 2);
  INSERT 0 1
  nworks=# insert into todo values(4, 'call mom', 3);
  INSERT 0 1
  nworks=# select * from todo;
   todo_id |     task      | preceded_by 
  ---------+---------------+-------------
         1 | do homework   |           1
         2 | clean bedroom |           1
         3 | walk the dog  |           2
         4 | call mom      |           3
  (4 rows)

  
  nworks=# update todo set preceded_by = 1 where todo_id = 4; -- call your mother first, it's more important
  UPDATE 1
  
  nworks=# select * from todo order by preceded_by;
   todo_id |     task      | preceded_by 
  ---------+---------------+-------------
         2 | clean bedroom |           1
         1 | do homework   |           1
         4 | call mom      |           1
         3 | walk the dog  |           2
  (4 rows)

I didn't do any operation to fix the sorting order once I updated item #4, so there's more work to do here. I don't think it's a practical solution.

[grzm]

If you’re using a surrogate key in the preceded_by column, you won’t be able to reorder items in the list without updating the relevant keys, which obviates the use of the surrogate key (which is meant to be immutable).