Record order, what is the best approach?

You probably don't need to move your ordering values out to a different table, unless they will be used in multiple places and have a different position depending on which list they're in.

Usually, I find, the easiest solution to this is to use a signed integer, call it 'weight' and default weight values to 0. Then as things are reordered lower values float to the top, and higher values sink to the bottom.

In that way you can simply order by weight ASC and have the explicitly defined order, with non-explicitly set items falling where they may in the list. Or you could order by weight then by post date, or title, or some other secondary sort.

Anyway, no.. I don't think a pivot table is necessary here at all.

Thank you... @willvincent I like your approach, having the records ordered by their 'weight' sounds the simplest solution. I'll wait for a while to see if I get more recommendations.

I use the javascript library dragular http://bevacqua.github.io/dragula/ to allow the user to reposition the items.

Then you can either fire an event at each move that using ajax renumbers the order on each item, or you can have a save button and post it like a form.

I have each item in an input type=text field with a name like item[]. Dragular is set so that the input elements can be rearranged and when save is pressed, the array that comes to the controller has an array of items already in top to bottom order. I just then write the position of these back to the database with an incrementing weight.

They can then later be retrieved from the database in weight order.

It works rather well!