title: Storing item positions (for ordering) in a database efficiently
body: <p>Scenario:</p>
  
  <p>There is a database of movies a user owns, movies are displayed on a page called "my-movies", the movies can be displayed in the order that the user desires. For example "Fight Club" in position #1, "Drive" in position #3 and so on and so forth. </p>
  
  <p>The obvious solution is to store a position with each item, for example:</p>
  
  <p>movieid, userid, position<br>
  1      | 1     | 1<br>
  2      | 1     | 2<br>
  3      | 1     | 3  </p>
  
  <p>Then when outputting the data is ordered by the position. This method works fine for output however it has a problem when updating: the position of an item all the other positions need to be updated because positions are relative. If movie #3 is now in position number 2 then movie #3 now needs to be updated to position #2. If the database contains 10,000 movies and a movie is moved from position #1 to position #9999 that's almost 10,000 rows to be updated!</p>
  
  <p>My only solution is to store positioning separately, instead of having an individual field for each items position it's just one big data dump of positions that are taken in run time and associated with each item (json, xml, whatever) but that feels... inefficient because the database can't be left to do the sorting.</p>
  
  <p>My summarised question: <strong>What's the most efficient way of storing items positions in a list that is friendly to fetching and updating?</strong></p>