I'm trying to work out the schema for a system where users will be able to order items in an (unlimited length) list by drag and drop. Is there any kind of standard way of storing this ordering information, without updating every record with every change?

So far I've come up with two possibilities:

  • Using a float field, with default value for adding to the end of the list being higher than the last item in the list, and updating the any moved element to have a value halfway between the one above and the one below - the problem here is it'd be possible for ever-more precise values to cause problems as they reach the limits of the field. Advantages are it's easy to sort.

  • Having a before_id (with the topmost element set to null), set to the id of the preceding element. Sorting in this case would take an extra step or joins.

Is there a better way of doing this I'm missing?

  • I recently implemented a drag-and-drop interface and was able to use int for sort order, largely because the RDBMS was SQL Server, which can take a table as a stored procedure parameter. This enabled a MERGE on the sorted items, which is scalable beyond the practical limits of the drag-and-drop UI. – dartonw Dec 14 '14 at 16:32
  • 1