DEV Community

Daniel da Rocha
Daniel da Rocha

Posted on

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

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 user would be able to filter data by award (Oscars, Golden Globe, etc), by year, by categories, etc.

That would require a database to store all the award and movie data. The movie data will be retrieved from The Movie Database API, but I still need to store some of it (TMDB id, name, year, poster, etc) to create the relationship of each movie with the specific awards it's in.

Therefore I need an "awards" database. It should store award information, dates, categories, nominees, winners, etc.

I was thinking of going the easy way (for me!) and use SQLite for that. It is a very predictable database, and will be updated a few times a year only.

But then I got lured into the NoSQL discussion.

As I am planning to, on a v2.0, allow users to save movies in a watchlist, or mark as watched, I though this would be a great use for NoSQL?

Please help! What would you do? Use both? focus on SQL or NoSQL only?

Thanks!!
Best,
Daniel

Top comments (10)

Collapse
 
dmfay profile image
Dian Fay

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.

Collapse
 
danroc profile image
Daniel da Rocha

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.

Collapse
 
danroc profile image
Daniel da Rocha

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

Collapse
 
dmfay profile image
Dian Fay

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.

Collapse
 
francisco profile image
Francisco M. Delgado • Edited

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!

Collapse
 
rhymes profile image
rhymes

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!

Collapse
 
kspeakman profile image
Kasey Speakman • Edited

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.

Collapse
 
patrickodacre profile image
Patrick O'Dacre

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.

Collapse
 
danroc profile image
Daniel da Rocha

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!

Collapse
 
theoutlander profile image
Nick Karnik • Edited

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