database design for song position under play-list 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.
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  |
+------+------------+----------+