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

Instead of Position field you can use a field as LastSongId where you can add the SongId of the previous song in the play list.

So when you want to change you order you only need to update only three rows.

The first row: Your song with new order

The second row / the third row: The song before and after your your changed position song

Your new schema:

+------+------------+----------+
|SongId| PlaylistId |LastSongId|
+------+------------+----------+
|   1  |     10     |    NULL  |
|   2  |     10     |    1     |
|   3  |     10     |    2     | 
|   5  |     10     |    3     | 
|   6  |     11     |    NULL  |
+------+------------+----------+

If you change the SongId 3 as position 1, you'll have:

+------+------------+----------+
|SongId| PlaylistId |LastSongId|
+------+------------+----------+
|   1  |     10     |    3     |
|   2  |     10     |    1     |
|   3  |     10     |    NULL  | 
|   5  |     10     |    2     | 
|   6  |     11     |    NULL  |
+------+------------+----------+

Answer 2

If you leave gaps one way or another, you can postpone a massive update until later. Using an integer with gaps:

-- init
update PlaylistSongMapping set Position = 10000 * position;

-- move songId = 3 to first position
update PlaylistSongMapping set Position = 5000 
where SongId = 3
  and PlaylistId = 10;

Another option is to use a decimal, but the idea remains the same.

Yet another option is to add a secondary order attribute:

ALTER TABLE PlaylistSongMapping
    ADD COLUMN Position_secondary INT DEFAULT 1000 NOT NULL;

-- move songId = 3 to first position
update PlaylistSongMapping 
    set Position = 1
      , Position_secondary = 500  
where SongId = 3
  and PlaylistId = 10;

-- Get songs in order:
select * from PlaylistSongMapping
order by PlaylistId, Position, Position_secondary