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?
