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

Which for scrobbled data I think that was good enough, I added also a album cover table so I can get some nice pics alongside the song. 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

Then I was like, let's make Album and Artist their own entity as well

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.

For posterity let's also set up ArtistAlbum so we can assocaite multiple artists credited an album’s ownership

Oh, also there's explicit versus non-explicit song releases, lets add a is_explicit as well.

But hey, what if the song we want is on multiple albums? How do we define it then?

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.

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...thanks Taylor.

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.

Now what if an artist released the same song, with the metadata claiming the same name and release date….seriously....

Role Model by Eminem has two versions, but in the Apple Music metadata it retains the same release date....

I guess we can check uniqueness with also songDuration

You know, why is it that Role Model has two different durations and two versions that are both explicit?

After some digging (listening) even though the longer one is labeled as explicit, it's mostly censored, so probably the music distortion adds 5s. I am not sure why Apple labels it as explicit though, maybe because of how violent it is as a song? Maybe it was a mistake on their end ¯_(ツ)_/¯

But anyway, what if all of those above are true, and also the two versions are the exact...same...duration...thanks Taylor...

1. 
   ME! — Taylor Swift feat. Brendon Urie
2. 
   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