Skip to content
loading...

Should I go NoSQL or SQL for my specific app case? Or both?

Daniel da Rocha on February 06, 2018

Hi everyone! I am designing a movie-recommendation iOS app based on Movie Awards. It would only show movies that were nominated to awards, and the... [Read Full]
markdown guide
 

The short version: if you can't articulate, right here and right now, why you need to use a non-relational database, you should use a relational database.

In your specific case, you're already talking about slicing movie data multiple ways and applying award information. That's clearly a join between two tables. You have relational data, and if you try to shoehorn it into a non-relational schema you will eventually regret it.

If you want to represent certain data as documents (I'm not sure user watchlists are the best example here, since they still constitute relationships between users and movies) then look into Postgres' JSON functionality which allows you to blend relational and document strategies quite effectively.

 

You are right, I can articulate exactly why a relational DB is suitable for my needs, but not the other way around. Clear signal :)

I was just wondering if I was missing something. Thanks for clarifying, and I'll def look into Postgres's JSON functionality.

 

Alright, thanks for all the replies! :)

As it stands now, I am looking at the following setup:

  • MySQL DB in the backend to hold all information about awards and tie them to movies with specific TheMovieDatabase IDs
  • The app will hold a snapshot of all data on an SQLite DB (for speed and offline access), which should be updated whenever new data is added/modified in the server end (this should not happen frequently (only about once or twice a month)
  • Firebase to handle user authentication, user management, Watchlists and Watched lists

Some conclusions I reached by reading around:

  • PostgreSQL seems overkill for such a simple application
  • I do not need the scalability or speed NoSQL seems to offer as the database is simple and only occasionally updated
  • I might eventually add text search (ElasticSearch seems super interesting), but for now, I will only work with filtering based on switching things on and off ("show only OSCARS", or show only the "awards for best movie"). In the future I'll probably add movie search....

Does any of the above sounds wrong/bad/weird to you, fellow experience devs?? :)

Thanks!
Best,
Daniel

 

Sounds like a cool project. I made a really simple app with the movieDB api when I was first learning iOS. I've been meaning to do an app with this API again. Where you can tweet a movie review from the movie app along with the movie poster.

You might be able to get away with just CoreData and a MovieDB client without using PostgreSQL (assuming you're building a native app). But that's just my two cents. I'm still learning!

 

Why are you considering using three databases for a simple application? Start with one, and break specific domains out if and when you need to.

Scalability and speed are not inherent attributes of NoSQL databases. Many of them are designed with an eye to both, but it's not to be taken for granted. Something like Cassandra, for example, would be very bad for your application because it's optimized for high write volume and you're mostly reading. HBase would be bad because you can't do ad-hoc queries easily. Meanwhile, relational databases can scale well enough until you're Amazon or Google tier (even Instagram runs on Postgres), and designed correctly they're plenty fast.

Last, if you're considering MySQL you should think about Postgres; they're in the same weight class, and I would use Postgres over MySQL in a heartbeat. Overkill only matters when you add unnecessary complexity, such as for example splitting your data layer across three independent DBMSs before you've even gotten anything off the ground.

 

Dian said it all.

I would like to add that after you designed your models, tables and relations and eventually used JSON for "non structured data" you might want to use full text search.

Consider PostgreSQL's own FTS before diving into elastic search or others: rachbelaid.com/postgres-full-text-...

Also since PostgreSQL 10 you have FTS on JSON columns: wiki.postgresql.org/wiki/New_in_po...

Have fun!

 

Here are some deciding factors I might use:

Am I dealing with sets of data and set membership?
i.e. Which vehicles are in the SUV category?

Relational

Do I have nested objects which are saved and loaded as one?
a.k.a. Do I feel forced to use an ORM to scatter and reconstitute an object from tables? i.e. a company and all its addresses and contacts.

Document or Key/Value

Do I need to answer questions about how things are arbitrarily related?
i.e. Who likes the same Dunkin Donuts as Alice?

Graph

There are a lot of other kinds of databases (temporal, geospatial, columnar, search, etc). And lots of other considerations you could go by.

Sounds to me like relational (SQL) is still about the best fit for most of it (especially the watch lists).

+1 for Postgres... in addition to SQL it can do JSON document or KV storage, and full text search is pretty easy / effective. I would also pick Postgres over MySQL. Last I used MySQL was 5.x, but it was pretty terrible as a relational database.

 

NoSQL databases like Mongo support relations via Mongoose, so that is one of my picks. However, I would strongly recommend looking into ElasticSearch because it has recommendation engine add-ons as well. Normally, I would suggest Neo4J, but it is a bit more complex to work with compared to ElasticSearch, but you can check that out too.

The long answer is that I would focus on the use-case first and look at how complex the joins need to be. Can that data be stored successfully in a single document or split into multiple documents where a single join would allow me to retrieve data? How are you planning on querying it? Is it real-time queries mostly? Or, offline queries for data processing? Those things will make it clear which way you should go. How much data will you be storing? Mongo and ElasticSearch scale better and are easier to work with.

I've worked with a variety of SQL databases for over 20 years - SQL Server, MySQL, Access, Postgres, and Oracle. I have used a few NoSQL databases too, but have stuck to Mongo as a Document DB. I also love ElasticSearch for search-based applications and Neo4J for graph-based applications. I rarely go back to using SQL databases, because they are a pain in the ass to deal with on fast-paced projects and slow you down.

Check this talk by Martin Fowler on NoSQL - youtube.com/watch?v=qI_g07C_Q5I

 

oh boy :) This is something I have wrestled with a lot!

I decided to use MySQL over MongoDB for the following reasons:

1) I already knew a bit about MySQL
2) MySQL would definitely handle the job
3) There were relationships in my data, and I didn't want to manage those relationships manually using MongoDB.
4) There was simply no compelling reason to use MongoDB, and with the lack of a clear reason, I had to go with what I knew to stay productive.

The way I see it, if your goal is to create an app, as opposed to learn some NoSQL db, and there is no clear advantage to using NoSQL, your best bet is to get the job done.

It sounds like MySQL or other SQL db like Postgres would be more than capable of handling your requirements.

 

Yes, this is what I've been wondering as well: is NoSQL really a valid option or am I just excited about learning something new?

I guess I prefer to get the job done, as there are really no compelling reason to go the other way!

code of conduct - report abuse