Storing item positions (for ordering) in a database efficiently

Question

Scenario:

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.

The obvious solution is to store a position with each item, for example:

movieid, userid, position
1 | 1 | 1
2 | 1 | 2
3 | 1 | 3

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!

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.

My summarised question: What's the most efficient way of storing items positions in a list that is friendly to fetching and updating?

Answer 1

If you use a combination of the position and a timestamp that the user put a movie in a given position rather than trying to maintain the actual position, then you can achieve a fairly simple means of both SELECTing and UPDATEing the data. For example; a base set of data:

create table usermovies (userid int, movieid int, position int, positionsetdatetime datetime)

insert into usermovies (userid, movieid, position, positionsetdatetime)
values (123, 99, 1, getutcdate())
insert into usermovies (userid, movieid, position, positionsetdatetime)
values (123, 98, 2, getutcdate())
insert into usermovies (userid, movieid, position, positionsetdatetime)
values (123, 97, 3, getutcdate())
insert into usermovies (userid, movieid, position, positionsetdatetime)
values (123, 96, 4, getutcdate())
insert into usermovies (userid, movieid, position, positionsetdatetime)
values (123, 95, 5, getutcdate())
insert into usermovies (userid, movieid, position, positionsetdatetime)
values (123, 94, 6, getutcdate())

insert into usermovies (userid, movieid, position, positionsetdatetime)
values (987, 99, 1, getutcdate())
insert into usermovies (userid, movieid, position, positionsetdatetime)
values (987, 98, 2, getutcdate())
insert into usermovies (userid, movieid, position, positionsetdatetime)
values (987, 97, 3, getutcdate())
insert into usermovies (userid, movieid, position, positionsetdatetime)
values (987, 96, 4, getutcdate())
insert into usermovies (userid, movieid, position, positionsetdatetime)
values (987, 95, 5, getutcdate())
insert into usermovies (userid, movieid, position, positionsetdatetime)
values (987, 94, 6, getutcdate())

If you query the user's movies using a query like this:

;with usermovieswithrank as (
  select userid
  , movieid 
  , dense_rank() over (partition by userid order by position asc, positionsetdatetime desc) as movierank
  from usermovies
)
select * from usermovieswithrank where userid=123 order by userid, movierank asc

Then you'll get the expected result:

USERID  MOVIEID     MOVIERANK
123     99          1
123     98          2
123     97          3
123     96          4
123     95          5
123     94          6

To move one of the rankings of the movies we need to update the position and the positionsetdatetime columns. For example, if userid 123 moves movie 95 from rank 5 to rank 2 then we do this:

update usermovies set position=2, positionsetdatetime=getutcdate() 
where userid=123 and movieid=95 

Which results in this (using the SELECT query above following the update):

USERID  MOVIEID     MOVIERANK
123     99          1
123     95          2
123     98          3
123     97          4
123     96          5
123     94          6

Then if userid 123 moves movie 96 to rank 1:

update usermovies set position=1, positionsetdatetime=getutcdate()
where userid=123 and movieid=96 

We get:

USERID  MOVIEID     MOVIERANK
123     96          1
123     99          2
123     95          3
123     98          4
123     97          5
123     94          6

Of course you'll end up with duplicate position column values within the usermovies table, but with this method you'll never show that column, you simply use it along with positionsetdatetime to determine a sorted rank for each user and the rank you determine is the real position.

If at some point you want the position column to properly reflect the movie rankings without reference to the positionsetdatetime you can use the movierank from the select query above to update the usermovies position column value, as it wouldn't actually affect the determined movie rankings.

Answer 2

I've been struggling with what best to do with this situation and have come to the realisation that BY FAR the best solution is a list/array of the movies in the order you want them eg;

userId, moviesOrder

1 : [4,3,9,1...]

obviously you will serialise your array.

'that feels... inefficient'?

consider the user had a list of 100 movies. Searching by position will be one database query, a string to array conversion and then moviesOrder[index]. Possibly slower than a straight DB lookup but still very very fast.

OTOH, consider if you change the order;

with a position stored in the db you need up to 100 row changes, compared to an array splice. The linked list idea is interesting but doesn't work as presented, would break everything if a single element failed, and looks a hell of a lot slower too. Other ideas like leaving gaps, use float are workable although a mess, and prone to failure at some point unless you GC.

It seems like there should be a better way to do it in SQL, but there really isn't.

Answer 3

Store the order linked-list style. Instead of saving the absolute position, save the ID of the previous item. That way any change only requires you to update two rows.

movieid | userid  | previousid
   1    |    1    | 
   2    |    1    |    1
   3    |    1    |    4
   4    |    1    |    2

To get the movies in order ...

SELECT movieid WHERE userid = 1 ORDER BY previousid

-> 1, 2, 4, 3

To (say) move #4 up a space:

DECLARE @previousid int, @currentid int
SET @previousid = SELECT previousid FROM movies WHERE movieid = @currentid

-- current movie's previous becomes its preceding's preceding
UPDATE movies SET previousid = 
    (SELECT previousid FROM movies WHERE movieid = @previousid)
WHERE movieid = @currentid

-- the preceding movie's previous becomes the current one's previous
UPDATE movies SET previousid = @currentid WHERE movieid = @previousid

That's still 1 read + 2 writes, but it beats 10,000 writes.

Answer 4

ID   NAME  POSITION
7     A       1
9     B       2
13    C       3
15    D       4
21    F       5

Given the current scenario if we want to move item D to position 2 we can search for the interval between 2(the position we want to move the item) and 4 (The item's current position) and write a query to ADD +1 to the position of every element inside this interval hence in this case we can do the following steps:

1. Search for items in the interval where position >= 2 AND position < 4, and add a +1 to its position
2. Set Item D position to 2.

This will generate that know : A->1, B->3, C-> 4, D->2, F->5

In case we want to move B to D then we need to do the opposite and apply a -1 instead.

1. Search for items in the interval where position > 2 AND position <= 4 substract -1 from its position
2. Set item position to 4

When deleting an Item from the table we need to update every item where its position is greater than the position of the element that's being deleted.

And when creating and Item its position is equal to the COUNT of every item +1.

DISCLAIMER: If you have a really big amount maybe this solution is not what you want, but for most cases will do. Normally a user wont move an item from position 10000 to position 2 but if instead the users delete item 1 then the query will substract -1 to the 9999 remaining items. If this is your scenario then maybe the solution with the linked list is probably the best for you, but then ordering will be more challenging because you need to go item by item to see who's next on the list.

Example querys

-- MOVE DOWN
UPDATE movie SET position = position-1  WHERE position <= 18 AND position > 13 AND id > 0;
UPDATE movie SET position = 18 WHERE id = 130;

-- MOVE UP
UPDATE movie SET position = position+1  WHERE position < 18 AND position >= 13 AND id > 0;
UPDATE movie SET position = 13 WHERE id = 130;

Answer 5

Really interesting solutions here. Another possibility might be to store positions with some space, say multiples of 10 or 100.

ID   NAME  POSITION
7     A       100
9     B       200
13    C       300
15    D       400
21    F       500

This multiple of 100 can be done for every new addition. Then moving a row C to position 1, would be -1 the current value or +1 after the current value. Or even -50 so that the same can be possible in future.

ID   NAME  POSITION
7     A       100
9     B       200
13    C       50
15    D       400
21    F       500

This can be continued, and in cases of so many movements that it is not possible, then a reorder of all the rows is done once again.