Database Patterns: What's the standard way for manually sorting a table?

Question

I have a (Postgres) DB table that I'd like to add a manual 'sort' field to. In the front end of the application, I'll have a drag/drop field so that users can manually re-sort the entries, which should then post an AJAX request that re-sorts the entries in the DB and I'm just wondering how to orchestrate this in the database.

For example, the most obvious option I can think of would be to increment the 'sort' integer for every entry with a sort value >= the newly sorted option, but that's going to be excessively (and I assume, unnecessarily) heavy on the database if these lists grow above a handful of items.

Another option would be to make the 'sort' column a BigDecimal and make it's value

SortValue[A] = SortValue[B] + (SortValue[C] - SortValue[B])/2

Where A is the field I'm re-sorting, B is the field directly above it and C is the field below it, but that seems a really messy solution, not to mention potentially restricted by decimal place limits.

I'm sure this is a really common problem. What's the standard way of efficiently allow manual sorting of a database table?

Cheers...

Answer 1

Suppose you have some data like this:

id | pos
---+----
 8 |  1
 3 |  2
 6 |  3
 7 |  4
 2 |  5
 1 |  6

and you want to move 2 from position 5 to position 3.

All you need to do is this:

update t set pos = pos + 1 where pos >= 3 and pos < 5

To make a hole:

id | pos
---+----
 8 |  1
 3 |  2
   |
 6 |  4
 7 |  5
 2 |  5
 1 |  6

And then this:

update t set pos = 3 where id = 2

to fill the hole:

id | pos
---+----
 8 |  1
 3 |  2
 2 |  3
 6 |  4
 7 |  5
 1 |  6

You would, of course, have all those updates wrapped in a transaction.

If you have the pos constrained to avoid duplicates (a good idea) then you could use pos = 0 as a temporary value:

update t set pos = 0 where id = 2;
update t set pos = post + 1 where pos >= 3 and pos < 5;
update t set pos = 3 where id = 2;

Alternatively, if you're using a recent version of PostgreSQL (AFAIK 9.0+), you could defer your unique constraint to the end of the transaction and not have to worry about the temporary duplicate.

The other cases are similar and left as an exercise.