1

I’m building a prototype app that leverages the Spotify API. https://developer.spotify.com/discover/

I’m doing research on choosing the database architecture. Since this is a prototype I’d like to keep it simple yet still have a path forward for future growth.

If you have worked with the Spotify API and storing retrieved data I’d love to hear from you.

Also, I have read many of the posts regarding choices between databases and have edited this question based on reading them. I am asking again because some of the posts were several years old and things change.

My question is: What are the advantage and disadvantages of the options I propose in this post and what other options might be a good fit for this type of application?

Overview The application will store data retrieved from Spotify. The Spotify API returns data in JSON format.

The application will be store User data for hundreds and, hopefully, thousands of users.

Playlists and tracks are the primary data. Playlists can contain up to 100 or more tracks. Every user will have multiple playlists/tracks stored. After one year a user might have 50 to 100 playlists each with a number of tracks.

Stored playlists will be displayed to users in the app/web and used to create other playlists. Tracks will have data added to them on a daily basis e.g. date, rank.

There will be transactions, such as a playlist being locked against further edits, closing out periods, closing out rankings, etc..

Spotify Category Outline

User: Email, product, display name, birthdate, image https://developer.spotify.com/documentation/web-api/reference/users-profile/get-users-profile/

Music metadata: Albums, artists, and tracks

Playlists: Get a playlists tracks and store them. You can see what the JSON data looks like here: https://developer.spotify.com/documentation/web-api/reference/playlists/get-playlists-tracks/

Database Options

Option A Store the Spotify JSON data in a Relational database such as mySQL or MariaDB.

Both mySQL and MariaDB support JSON data types though they manage them slightly differently. According to the MariaDB KB: In MySQL, JSON is an object and is compared according to json values. In MariaDB JSON strings are normal strings and compared as strings.

What are the advantages/disadvantages of, first, storing JSON in a relational database, for instance can it be queried adequately? A sample query might be, “List the user’s playlists by rank”. Second, what are the ads/disads between mySQL and MariaDB’s management of JSON?

What other issues should I be looking at with relational dbs?

Option B Spotify uses the Cassandra database to deliver the Spotify service. While this is a prototype app might it be worthwhile to consider Cassandra or some other Not Only SQL type of database? How well does Cassandra import JSON data? Are there developers readily available that understand how to work with Cassandra?

Option C, D, E…

What other database options might be considered for data structure for this type of application?

Thanks in advance!

Eric Langley
  • 183
  • 1
  • 2
  • 9
  • Just a note - you say your table will contain "User: Email, product, birthdate"... and yet the link you gave https://developer.spotify.com/documentation/web-api/reference/users-profile/get-users-profile/ shows that the user object you get back from Spotify contains none of these things. And nor should it - they're private! Are you planning to get people to submit this to you separately? – ADyson Dec 17 '18 at 22:27
  • BTW your question is far too broad...you're essentially asking people to carry out an options appraisal / technical product assessment on your behalf, all for the joy of 15 points reputation. This is a task you can spend a few days researching yourself online. – ADyson Dec 17 '18 at 22:29
  • Why use JSON at all? Users, playlists, albums, artists, tracks, etc -- these are the types of Entities that RDBMSs love to "Relate". And they easily handle "arrays" (such as tracks in an album) as _rows_, not strings buried in a JSON string. Once you make that leap of faith, any RDBMS can easily handle the data. You say "hundreds, hopefully thousands"? Even "millions" will be fine. – Rick James Dec 17 '18 at 22:46
  • Here is the Spotify User Profile API Endpoint that details the fields that I noted: https://developer.spotify.com/documentation/web-api/reference/users-profile/get-current-users-profile/ – Eric Langley Dec 18 '18 at 11:23
  • Rick, JSON is the format that the data comes in. I need to store, manipulate and present it. – Eric Langley Dec 18 '18 at 11:33

1 Answers1

3

If focused on data fields

If you are focused on the contained data values, rather than the JSON document, you should parse out the pieces of the JSON into specific tables and columns. If you are doing much querying on those pieces, or re-building those pieces such as for a user-interface, then build out a normalized relational database table structure.

If focused on JSON document as a whole

If you are not educated on relational database design, or if your app is focused on working with JSON documents as a whole, then use a database with support of JSON documents.

It appears MySQL has such support, but I do not know the details.

I do know that Postgres has excellent support for JSON, parsing the document upon insert, and storing the parts in its own native binary format: jsonb. This allows for all the powerful indexing offered by Postgres. You will also find powerful functions for manipulating JSON. As icing on the cake, Postgres also brings its renowned reliability.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
  • I am focused on the contained data values, I simply receive them as JSON. Thank you for the information about Postgres JSON import capability. There's some good info about that capability here: https://stackoverflow.com/questions/39224382/how-can-i-import-a-json-file-into-postgresql – Eric Langley Dec 18 '18 at 11:30