User-defined Order in SQL
Having run into this problem a few times now, Approach 1 is unfortunately best. Two reasons:<p>(1) If updates fail, you don't get a resulting broken state<p>(2) The size of the lists is not that large (<50), so the extra writes are not that expensive.<p>Essentially, with any variation on fractional updates (where only 1-2 rows are updated per list update), the client is sending to the server "move item a to position x". Then, shortly after, "move item b to position y", etc. And if any intermediate step fails, the server and client are out of sync.<p>If, instead, the client message is "here is the new state of the list: (a,b,c)" and the server updates, failed requests only leave the state temporarily out of sync. The client will continue to send the complete correct state at each update.