database design for song position under play-list

Question

I have a music streaming application where I have playlists. Each playlist has maximum 100 songs. I have a mapping table called PlaylistSongMapping which has the following schema

+------+------------+--------+
|SongId| PlaylistId |Position|
+------+------------+--------+
|   1  |     10     |    2   |
|   2  |     10     |    1   |
|   3  |     10     |    3   | 
|   5  |     10     |    4   | 
|   6  |     11     |    1   |
+------+------------+--------+

The position of each song inside playlist is managed using the Position column. Inside the playlist I need the order change functionality. The current logic is to update Position column. The problem is if i need to move the song with SongId: 3 to first position I need to update rows of SongId 1,2,3. This number would be a big one when playlist has more number of songs. Is there a better logic so that number of update queries are very minimum.

Answer 1

If you're after an SP that updates all relevant positions in the table when moving the position, this should do the job:

USE Sandbox;
GO

--Small sample set
CREATE TABLE dbo.Playlist (SongID int,
                           PlaylistID int,
                           Position int);
INSERT INTO dbo.Playlist (SongID,
                      PlaylistID,
                      Position)
VALUES(1,1,1),
      (3,1,2),
      (5,1,3),
      (2,1,4),
      (2,2,1),
      (10,2,2);
GO

--Check data
SELECT *
FROM dbo.Playlist;
GO

--CREATE the SP
CREATE PROC dbo.MoveSongPosition @PlaylistID int,
                             @SongID int,
                             @NewPosition int
AS BEGIN

    UPDATE P
    SET Position = CASE WHEN SongID = @SongID THEN @NewPosition ELSE Position +1 END
    FROM dbo.Playlist P
    WHERE P.PlaylistID = @PlaylistID
      AND (P.SongID = @SongID
       OR  P.Position BETWEEN @NewPosition AND (SELECT sq.Position
                                               FROM dbo.Playlist sq
                                               WHERE sq.SongID = @SongID
                                                 AND sq.PlaylistID = @PlaylistID));
END
GO

--Run and test the SP
EXEC dbo.MoveSongPosition @PlaylistID = 1,
                          @SongID = 2,
                          @NewPosition = 2;
GO

--Check the new data
SELECT *
FROM dbo.Playlist;
GO

--Clean up
DROP PROC dbo.MoveSongPosition;
DROP TABLE dbo.Playlist;

This solution assumes you are using SQL Server; you haven't updated your tags since my comment, so we have no idea what RDBMs you are really using.

Edit: Logic change, beleive I've corrected.

CREATE PROC dbo.MoveSongPosition @PlaylistID int,
                                 @SongID int,
                                 @NewPosition int
AS BEGIN

    UPDATE P
    SET Position = CASE WHEN P.SongID = @SongID THEN @NewPosition
                        WHEN P.Position = @NewPosition THEN P.Position + V.Direction
                        WHEN P.Position < @NewPosition THEN P.Position - V.Direction
                        WHEN P.Position > @NewPosition THEN P.Position + V.Direction
                   END
    FROM dbo.Playlist P
         CROSS APPLY (SELECT ca.Position
                      FROM dbo.Playlist ca
                      WHERE ca.PlaylistID = P.PlaylistID
                        AND ca.SongID = @SongID) CS
         CROSS APPLY (VALUES(CASE WHEN CS.Position < @NewPosition THEN -1
                                  WHEN @NewPosition < CS.Position THEN 1
                                  ELSE 0 END)) V(Direction)
    WHERE P.PlaylistID = @PlaylistID
      AND ((P.Position >= @NewPosition AND P.Position <= CS.Position
       OR   (P.Position >= CS.Position AND P.Position <= @NewPosition)));
END

Answer 2

Theoretically more optimal (but way harder)

If you think of that column as a sort order rather than an exact position, you could have some strategy of having gaps, so numbering like 10, 20, 30. Then, if you want to move the 3rd song to the first position, you can give it number 5, or you could even give it a negative number.

The problem is that you would still need to read all the data, and the implementation for the numbering becomes way more complicated, because now you need to check whether or not you would need to update nothing, or everything or maybe just a part of the list.

Quite simple (basically what you have now)

So, if I were you, I would keep it simple, and just update everything that needs updating. After all, there aren't that many lines per playlist (even if you would allow 1000 or more songs per playlist), and if you make this a bulk update, a database should be able to handle that just fine. You can shift all the rows at once, and then update the one row to its new position. That way, you will only need two statements: 1 to insert, update or delete a specific song, and 1 to shift whole or part of the list to keep the numbering subsequent.

Very simple (and probably still fast enough by far)

In the past I have implemented this in an even more lazy fashion, where I kept a sort order with a gap, having 2, 4, 6, 8 ... Then, when I wanted to update or insert at a certain position, I could just use sortorder = position*2-1 for the row to insert or update, or simply delete any row:

update Song
set 
  SortOrder = :NewPosition*2-1
where
  SortOrder = :OldPosition*2;

After that, I simply updated all rows to fix the numbering again, regardless where my modification was, generating a new sequence based on the sort order.
This would mean that I did redundant updates of rows that were already correct, but it was really, really easy, still very fast (because databases are good at stuff like that), and it had some auto-repair effect as well, because the entire list would be renumbered every time, correcting any mistakes from the past. It does depend on your database how to implement that exactly. I was using Oracle, which was quite good and generating such sequences. In MySQL it is slightly more cumbersome, but still not that hard.

Answer 3

"The problem is if i need to move the song with SongId: 3 to first position I need to update rows of SongId 1,2,3"

You shouldn't need more than two statements for this.

You can update all the position values (apart from the one you're moving to first position) in one statement by using increment/decrement as appropriate, and then use a second statement to update the specific song you're moving - e.g.

update PlaylistSongMapping 
set Position = Position + 1 
where 
  Playlistid = 10 
  and position < 3 and position >= 1; 

update PlaylistSongMapping 
set position = 1 
where songId = 3;

This assumes you know the Id of the song you're moving (and therefore you can determine its current position, which you need for the where clause in the first query) - but it should always be the case that you know that, based on your description.

Note that in the above example the position >= 1 isn't strictly necessary since 1 is the first position in the list, but this code aims to cover a more general case - e.g. if you wanted to move it to position 2, you wouldn't want to include the song at position 1 in the increment, so in that case you'd be writing position >= 2 instead.