Oh man, music data structures are a rabbithole
I added a new feature to my website that automatically shows my current played and historically scrobbled music. I built it from the ground up meaning that I built my own scrobbler (which right now just sniffs my desktop Apple Music and pulls recent Youtube Music) and made a DB and API to store and present this.
When I was drawing out the data structures behind my tables, I started off pretty simple, I was like:
You can hover over each song to see how the data is appliedYou can click each song to see how the data is applied
- Aerodynamic — Daft Punk
- Superhuman — NCT 127
- The One Time In Houston — Wale
- The Trooper — Iron Maiden
- I Won't Back Down — Tom Petty
| Artist | str |
|---|---|
| Name | str |
| AlbumName | str |
| Duration | float |
| StartedAt | str |
| ScrobbledAt | str |
| NormalizedAlbumName | str |
| Id | str |
No Song records
| image64 | str |
|---|---|
| NormalizedAlbumName | str |
No AlbumCover records
Which for scrobbled data I think that was good enough, I worked through it a bit, and realized I wanted to include an album cover, the autoscrobbler on desktop can rip the local images of Apple Music, so I decided to just get a base64 24x24 picture and store that as a new table and just do a join to pull it so we don’t need to resave a bunch of album art over and over again.
This means that for when I scrobble from Youtube or Apple Music, I would want to build a system that can automatically flatten the songs and albums so the least amount of data is duplicated.
Originally I tried to just simplify this situation by using UPC as a unique identifier, but the Youtube API does not return that, nor do I think most songs uploaded on youtube even if it's official actually have a UPC.
Instead we need to look at the song and album's intrinsic attributes to derive uniquness when we do lookups on our DB.
Now that I had my v1 done, I wanted to make this even more efficient, so I made Song and Scrobble two separate entities
- On My Way to Nordstroms (feat. Stockz) — Guapdad 4000
- Homecoming — Green Day
- Dirty Harry — Gorillaz
- American Psycho — grouptherapy.
| Name | str |
|---|---|
| AlbumId | str |
| AlbumName | str |
| ArtistName | str |
| Duration | float |
| SongId | str |
No Song records
| ScrobbledAt | str |
|---|---|
| StartedAt | str |
| SongId | str |
No Scrobble records
Then I was like, let's make Album and Artist their own entity as well
- MAGFEST — femtanyl
- Cave In — Owl City
| ArtistId | str |
|---|---|
| ArtistName | str |
No Artist records
| AlbumName | str |
|---|---|
| AlbumId | str |
No Album records
| Name | str |
|---|---|
| AlbumId | str |
| AlbumName | str |
| ArtistName | str |
| Duration | float |
| SongId | str |
No Song records
| ScrobbledAt | str |
|---|---|
| StartedAt | str |
| SongId | str |
No Scrobble records
But now we have multiple artists, and we want to do a join, we would need to set up a new relation SongArtist since those are many-to-many connections.
- High — Freddie Gibbs, Madlib & Danny Brown
- Fire Away — Madeon & Slayyyter
- Perfect Pinterest Garden (Ninajirachi & underscores Remix) — Porter Robinson, Ninajirachi & underscores
- Nice to meet you — PinkPantheress & Central Cee
- Opposite Day — FIG & Yoshi T.
| SongId | str |
|---|---|
| ArtistId | str |
No SongArtist records
| ArtistId | str |
|---|---|
| ArtistName | str |
No Artist records
| AlbumName | str |
|---|---|
| AlbumId | str |
No Album records
| Name | str |
|---|---|
| AlbumId | str |
| AlbumName | str |
| ArtistName | str |
| Duration | float |
| SongId | str |
No Song records
| ScrobbledAt | str |
|---|---|
| StartedAt | str |
| SongId | str |
No Scrobble records
For posterity let's also set up ArtistAlbum so we can assocaite multiple artists credited an album’s ownership
- letstalkaboutit — Aminé, KAYTRANADA & Freddie Gibbs
- Downgrade — Guapdad 4000 & !llmind
- Otis — Kanye West & JAY-Z feat. Otis Redding
- A$AP Ferg — NAV & Metro Boomin feat. Lil Uzi Vert
| ArtistId | str |
|---|---|
| AlbumId | str |
No ArtistAlbum records
| SongId | str |
|---|---|
| ArtistId | str |
No SongArtist records
| ArtistId | str |
|---|---|
| ArtistName | str |
No Artist records
| AlbumName | str |
|---|---|
| AlbumId | str |
No Album records
| Name | str |
|---|---|
| AlbumId | str |
| AlbumName | str |
| ArtistName | str |
| Duration | float |
| SongId | str |
No Song records
| ScrobbledAt | str |
|---|---|
| StartedAt | str |
| SongId | str |
No Scrobble records
But hey, what if the song we want is on multiple albums? How do we define it then?
- Bohemian Rhapsody — Queen
- Bohemian Rhapsody — Queen
- Bohemian Rhapsody — Queen
- Bohemian Rhapsody — Queen
| AlbumSongId | str |
|---|---|
| SongId | str |
| AlbumId | str |
No AlbumSong records
↓ Show existing fields ↑ Collapse existing fields
| |||||||
| AlbumSongId | str | ||||||
|---|---|---|---|---|---|---|---|
No Scrobble records
| SongId | str |
|---|---|
| Name | str |
No Song records
| AlbumName | str |
|---|---|
| AlbumId | str |
No Album records
But what if there’s multiple versions of the same song…on the same album...
I guess we can treat them each as individual songs, but we still need to create some uniqueness here. Apple Music gives us a SongReleaseDate, let's use that, we can probably pull the same from YouTube's upload date, though TBH, this is most likley not going to work given Youtube Music's APIs.
- Bodys — Car Seat Headrest
- Bodys — Car Seat Headrest
↓ Show existing fields ↑ Collapse existing fields
| |||||||
| SongReleaseDate | str | ||||||
|---|---|---|---|---|---|---|---|
No Song records
| AlbumSongId | str |
|---|---|
| SongId | str |
| AlbumId | str |
No AlbumSong records
↓ Show existing fields ↑ Collapse existing fields
| |||||||
| AlbumSongId | str | ||||||
|---|---|---|---|---|---|---|---|
No Scrobble records
| AlbumName | str |
|---|---|
| AlbumId | str |
No Album records
What if there’s a rerelease of an album then? Guess I need to do that as well...turns out though, most albums rereleased do acutally change their name, besides Fearless (Platinum Edition).
But the metadata actually claims it's release date was the original date, I guess the track count is different, lets just add that as a field.
- Love Story — Taylor Swift
- Love Story — Taylor Swift
↓ Show existing fields ↑ Collapse existing fields
| |||||
| TrackCount | int | ||||
|---|---|---|---|---|---|
No Album records
↓ Show existing fields ↑ Collapse existing fields
| |||||||
| SongReleaseDate | str | ||||||
|---|---|---|---|---|---|---|---|
No Song records
| AlbumSongId | str |
|---|---|
| SongId | str |
| AlbumId | str |
No AlbumSong records
↓ Show existing fields ↑ Collapse existing fields
| |||||||
| AlbumSongId | str | ||||||
|---|---|---|---|---|---|---|---|
No Scrobble records
Now what if an artist released the same song, across both music and YouTube with the metadata claiming the same name and release date….seriously, 1999 was remastered, but in the metadata it retains the same release date....
I guess we can check uniqueness with also songDuration
- 1999 — Prince
- 1999 — Prince
↓ Show existing fields ↑ Collapse existing fields
| |||||||||
| songDuration | float | ||||||||
|---|---|---|---|---|---|---|---|---|---|
No Song records
| AlbumSongId | str |
|---|---|
| SongId | str |
| AlbumId | str |
No AlbumSong records
↓ Show existing fields ↑ Collapse existing fields
| |||||||
| AlbumSongId | str | ||||||
|---|---|---|---|---|---|---|---|
No Scrobble records
| AlbumName | str |
|---|---|
| AlbumId | str |
No Album records
But what if it’s the same duration too…
- ME! — Taylor Swift feat. Brendon Urie
- ME! — Taylor Swift feat. Brendon Urie
Taylor Swift Removes “Spelling Is Fun” Part From “ME!”Chris DeVillestereogum.com
You know what, I don't think any of this actually matters, I think we probably can just get by with the second solution, it's just a service for just me anyway...shudders
点赞
收藏
点踩
分享