Spotify
WELCOME TO
SPOTIFY
Duration | June 2020 - July 2020
Role | Data Engineer
Class | INFO 330
0.0 | CLIENT OVERVIEW
TEAM
Timothy Joo
Ryan Siu
David Mai
TOOLS
Microsoft SQL Server
Azure Data Studio
Lucid Chart
Adobe Photoshop
Spotify is an independent subscription-based music and podcast-based streaming service. It was founded on April 23, 2006, in Stockholm, Sweden. It offers services varying from mobile devices, car applications, websites, smart devices, and desktop applications.
It has a monthly paid subscription of $9.99 and focuses on offering all possible types of music, podcasts, and playlists.
As of Q1 of 2020, Spotify has 286 million monthly active users.
1.0 | BUSINESS SPACE
Our database will be modeled after the streaming service Spotify - it will consist of artists, songs, genres, users, user playlists, etc. Our primary focus will be organizing this data in a fashion that allows for the storing of playlists and later implementation of a recommendation system for our users. It will also be done in a manner that is efficient, as we do not want redundancy within our database.
We want to encourage users to interact with one another within our platform. This focus is addressed through the construction of a ‘collaboration’ and ‘following’ relationship within our database.
2.0 | PROBLEM SPACE
Over the past two decades, music has evolved from being restricted to physical formats to being widely available through digital means. Because of this, there has been a transformation in the means of song acquisition - from a pay-per-song basis to a more modern streaming model.
Our business aims to tackle these two trends while catering to the user’s experience by providing additional features such as personalized recommendations, a social platform, and 24/7 access with the ability to listen to music on the go.
4.0 | FINAL ERD DESIGN
3.0 | INITIAL SKETCH ERD
The initial draft of our Entity Relationship Diagram focused on getting our key tables drawn and set up.
MAIN GOALS
Target key entities
Understand the primary keys, foreign keys, and attributes needed for each table
For our final ERD, we made a challenging adjustment that further defined the relationship between users and the playlists that they can access.
MAIN GOALS
Define the relationship on how users can access a playlist
Work through each table and finalize primary keys, foreign keys, and attributes.
6.0 | FINAL THOUGHTS
As my initial run at a database design project, using Spotify was an excellent opportunity to experience my newfound passion for data science. This platform is a tool that I utilize every day, so understanding how its transactions could get processed through a relational database system was incredible to create.
I look forward to more SQL database projects in the future as I know that this is just the very beginning.
