Forum Discussion
Fact / Dimensional Table Approach
I see what you mean.
I would look into separate bridge tables for bridge_track_artist (join on artist_id or artist) and bridge_track_genre (join on genre_id or genre). May require additional modeling work.
This would get rid of many to many but honestly, I'm not sure how much benefit you'll get from this, outside of knowing you've built a "best practice" model.
Your Artist and Genre dimensions should join fact tables 1 --> many. (Always 1 to many from dimension to fact)
I'm not sure I would have a Track dimension but I've not explored Spotify data, so I don't currently know what options are available for joins, i.e. is there an artist_id in the data.
How would that one-to-many dimension table apply here though? A genre dimension table could never be a one-to-many as songs can have multiple genres.... same thought process for artists.
Maybe this cannot be set up in traditional star-schema format as the fact table does not have duplicate IDs - just one entry per song.
I see what you mean.
I would look into separate bridge tables for bridge_track_artist (join on artist_id or artist) and bridge_track_genre (join on genre_id or genre). May require additional modeling work.
This would get rid of many to many but honestly, I'm not sure how much benefit you'll get from this, outside of knowing you've built a "best practice" model.