How best to organize the database for playlists with the music?

Practice in order to make the Django simple service by which people can make playlists of their favorite songs.

Exemplary logic:
There is a table containing the names of all the songs of all users, the table users, a table for playlists of all users. There is also an intermediate table to link the songs with playlists.

When registering the user for it creates a hidden playlist where you add all of his tracks (even if he did not choose any of the playlist). When the user adds a new track, which is not yet in the database, it is added to the main database of tracks and through a hidden playlist is bound to the user. If the track is in the database - then just bind to the user. At first glance, nothing complicated.

The problems started when it became necessary to order the tracks by date added for each user separately. Need to sample tracks go in order of adding each individual user, in General, the playlist of the user, and subsidiaries.

I have 2 ideas how to do this:
  1. Add a field with the date in a hidden playlist
  2. To remove all hidden playlists, create a staging table with foreign keys to user and track, and date added


What is the best option to choose? It is very important to ensure that the sample worked quickly with a large number of users, tracks and playlists.

Thanks for the replies.
March 20th 20 at 11:45
1 answer
March 20th 20 at 11:47
Solution
I would be repelled by the simplicity of building select query with sorting. As for me, here is the most simple option # 1, add a field with the date in the playlist. Then you can just do ORDER BY on an indexed field without any additional ties.
Need to sort by date added of the track to the database user in the hidden playlist. That is, if you request any playlist sort should be by date that is hidden from the playlist. - oliver.Feil commented on March 20th 20 at 11:50
@Austin81, if I understand the clarification, then the format is
hidden_playlist
- song_id
- date

user_playlist_1
- song_id -> hidden_playlist.song_id

then, I would have copied hidden_playlist.date user_playlist_1 at the time the song was added, i.e. here it would have made

user_playlist_1
- song_id -> hidden_playlist.song_id
- date -> hidden_playlist.date

as a result, for any user, you can always do a simple sort using only his playlist

A similar approach is commonly used in subscriptions to users for convenience, the construction of news feeds.

If I understand correctly, it is still my promise: to keep the maximum structured all the info for ease of selection for a particular user when replacing the duplicate data. - Alene_Renner commented on March 20th 20 at 11:53

Find more questions by tags PostgreSQLDatabase design