Comprehensive database schema for a Spotify-like music streaming platform covering artist profiles, album releases, track catalog with audio features, user-curated and editorial playlists, social following, listening history, liked songs, podcast integration, device management, and listening queue.
A track does not belong to an album here. tracks stands on its own with a primary_artist_id, and album_tracks connects the two with a disc number and a position. That is what lets the same recording appear on a single, on the album and on a compilation without being stored three times.
track_features is the table that makes recommendation possible: tempo, energy, danceability, valence and the rest, one row per track. It is separate from tracks because it is written once by an analysis job and read by a recommender, and has no business sitting on the row every play touches.
Follows are two tables — follows_artists and follows_playlists — rather than one polymorphic table. They point at different things, are read by different screens, and neither needs to know about the other, so a shared table would buy a type column and nothing else.
Making album_id a column on the track is simpler and wrong as soon as a song appears on a single and then on the album, or on a compilation years later. Each appearance would be a duplicate row with its own play count and its own likes. A junction with disc and position keeps one recording with one popularity, and the album is one of the places it appears.
Eleven numeric columns that no playback path reads do not belong on the row that every play, every search and every listing touches. Separating them also means the analysis job can rewrite a feature vector without competing with playback for the same row.
A play with no context tells you a track was heard. A play that knows it started from a playlist tells you the playlist works. context_type and context_id are two columns that turn a log into something a product decision can rest on, and they are cheap because they are not foreign keys — the context may be a radio station that has no table.
liked_songs is not a playlist row. It has no position and no owner other than the user, it cannot be shared, and it is read as a set rather than a sequence. Modelling it as a playlist would mean every playlist query carries a special case for the one that is not really a playlist.