0

I am thinking about making a music database. Something where you can search by artist, album or song. This seems to be a Many-to-many model, as an album can have multiple songs, and the same song can be on different albums.

Currently I am just using some JSON files, but this isnt really robust, as it can only represent hierarchical data.

This would just be a personal project, so I dont need anything too involved. I have looked at SQLite, but also Document-oriented databases. I like the idea of being able to persist the data as JSON or CSV files. What is a good option for my use case?


Response to some questions:

Do you want a free solution or need to use an enterprise level system like Microsoft SQL Server which costs money.

Free options only

How far do you expect to scale your database?...in other words, in the foreseeable future roughly how big do you expect it to get (data-wise), how many users do you expect will be using it both overall and at a given instance of time, how often will data change inside of it?

Most likely it would be a tool that only has one user at a time. So either I would use it myself, or someone might download my tool and use it for themselves.

What does your current technology stack look like?

I would prefer something with of of these if possible: C#, D, Go, Nim or Rust. Although I have seen many options in C and C++, so I might have to end up with one of those.

I don't think any modern solution out there persists the underlying data of the database in JSON or CSV files (though I could be mistaken).

I think Document-oriented database offer this, but I could be misunderstaning it. Also I had seen Export to CSV for SQLite.

1

Any answer to your question will be subjective and somewhat high level (since there's not a lot of details to go off of).

To clarify the purposes of a Document-oriented database system, it's purpose is for storing non-relational or loosely-structured data. It's not necessarily for actually directly storing files (such as JSON and CSV files) themselves. Rather it'll store loosely-structured data (such as what JSON can be used for) in it's own system (with it's own format under the hood).

When to choose an RDBMS vs a Document-oriented Database System (or a NoSQL database system)?

Choose a RDBMS if your data exhibits most of the following:

  • Strongly structured, i.e. concretely defined as opposed to loosely defined e.g., the database will have Artists, Albums, and Songs. The structure of each object (entity) type will be mostly similar across each instance of that object type
  • Each instance of a particular type of data shares mostly the same attributes e.g., all songs will have a Title, Artist, Song Length, and may have lyrics or a genre
  • Relationships are easily definable among the data, e.g. A Song belongs to one-or-many albums, one-or-many albums belong to an Artist
  • Your use cases involve various querying that'll involve filtering by different attributes of the data, e.g. You want to know how many Albums are particular Song is on? Or how many new Songs were created in 2019 by Artists of specific Genres? etc

Choose a Document-oriented / NoSQL database system if your data exhibits most of the following:

  • There isn't a strong structure to the objects and their properties

  • Or the structure will be vary various or highly changing over time

  • If you don't need to answer more complicates questions by querying the data via it's structure and relationship with itself

  • E.g. if some of your Songs will only have Titles, others will only have Genres, and some don't have Artists at all. Sometimes there will be Artists and sometimes there won't be any Artists, and you want to store the historical information about some Artists and their families sometimes. And you want to store all Albums and all Songs in the same place as the Artist they belong to (so when you read back from the database, it's all or nothing).

It sounds like you have a basic relational data use case (by inferring from your example), and while most modern types of database systems can probably handle your use case, the best fit is likely a Relational Database Management System (RDBMS). Examples of an RDBMS are SQLite, PostgreSQL, Microsoft SQL Server, Oracle PL/SQL, MySQL.

Each of the aforementioned RDBMS have their own pros and cons, and features that are available in one system that are lacking in another. Microsoft SQL Server and PostgreSQL probably are the most evolved of the examples, but for what sounds like a very basic use case, any of them would be applicable.

Things you should consider are:

  • Do you want a free solution or need to use an enterprise level system like Microsoft SQL Server which costs money. (Again, speculating, but your use case is very basic and likely can use of the free solutions available.)
  • How far do you expect to scale your database?...in other words, in the foreseeable future roughly how big do you expect it to get (data-wise), how many users do you expect will be using it both overall and at a given instance of time, how often will data change inside of it? SQLite is a great basic and free database system which could be perfect for your use case if you don't expect it to scale into a database that is billions of records big and petabytes of data with millions of active users, otherwise a more built out database system like the alternative examples I gave might be the better fit.
  • What does your current technology stack look like? Certain technologies are better fits and integrate more easily with certain database systems which might drive you to choose PostgreSQL over MySQL, or Microsoft SQL Server over Oracle PL/SQL. (E.g. Microsoft SQL Server works very nicely with Microsoft's technology stack: .NET & C#, etc).
  • Where do you plan to host the database?...Locally, Mobile Storage, or in the Cloud?...Linux or Microsoft Server? (These will filter down your possible options.)

I don't think any modern solution out there persists the underlying data of the database in JSON or CSV files (though I could be mistaken). Some of the examples I provided above can persist the data in the structured JSON format itself in the database. They also can ingest, digest, store, and export data to / from CSV and JSON files. For example, Microsoft SQL Server has functions for storing, manipulating, and reading back JSON within the database itself. It also can export data into various formats such as CSV.

There are also solutions, like in AWS, where you can store the files as JSON or CSV and then their database technologies utilize another AWS process to load the database from those files, but that's also applicable to other database systems like Microsoft SQL Server.

To directly answer your question (which again this is subjective) is if you're just getting starting with databases, SQLite is a pretty lightweight and easy system to start with. It's free, and is pretty developer friendly for someone who doesn't have a lot of experience writing SQL queries. You can at least get an idea of how databases work while getting started and still in the early phases of your project and could always port it over to any other database system if you choose to later (especially if your use case remains as simple as you described in your question).


For more information on Pros, Cons, and varying features of some of the example Relation Database Management Systems I mentioned above, these articles make for good reads:

To summarize some of the key information above that's relevant:

SQLite: Small footprint, user-friendly, portable BUT limited concurrency & scale, and lack of user management

MySQL: Popularity and ease of use, additional features like replication BUT known limitations (lacks FULL JOINs and other modern features), development of new features has slowed down, larger data concurrency issues

PostgreSQL: SQL compliance, open-source and community-driven, extensible BUT potential memory performance issues

Microsoft SQL Server: Very performant, flexible, lot of built in features out of the box BUT generally costs money (there is a minimalize free version)

3
  • 1
    thanks very much - I added some more info in the question – Steven Penny Dec 10 '20 at 23:00
  • @StevenPenny I'll try to clarify the part I wrote about "I don't think any modern solution out there persists the underlying data of the database in JSON or CSV files" as I might've misinterpreted what you were meaning. What I meant is the database system itself doesn't directly sit on top of CSV and JSON files, but most of the options I provided definitely can ingest, digest, stored, and output data from / to CSV and JSON files. – J.D. Dec 10 '20 at 23:03
  • Also to clarify the purposes of a Document-oriented Database System is they store semi-structure or structureless (more so NoSQL Database systems in that case) data. What that means is if your data doesn't have a concrete structure defined / doesn't have much relationships amongst itself, then a Document-oriented database is a better solution than RDBMS. They also have drawbacks for a lot other purposes that RDBMS are proficient at. You can think of a Document-oriented system almost having opposite purposes of a RDBMS. But they don't literally mean storing documents / files themselves. – J.D. Dec 10 '20 at 23:07

Not the answer you're looking for? Browse other questions tagged or ask your own question.