When to choose an RDBMS over a NoSQL option
Note: A couple of weeks ago I gave a presentation at work about the different types of data-stores available (Document, Key-Value, Graph DB, RDBMS, etc) and was asked to better explain myself as to when an RDBMS like MySQL should be chosen over a Document DB like MongDB. While I had an idea of what I wanted to say in my head, I struggled to put voice to my thinking. As a result, I determined that I needed to do a bit more research and get a more solidified answer. This post is the result of that research.
The data store world is currently a rather exciting place. New-ish technologies like MongoDB, CouchDB, Cassandra, DynamoDB, Neo4j, Riak, Redis, and others are giving us myriad choices in our data structure, no longer locking us into the 'old' relational database model. However, it is important, as always, to remember that just because we have new options, it doesn't mean any of them are better than the tools we already have. That consideration is especially true in considering RDBMS vs NoSQL, as most NoSQL solutions (excluding, perhaps, graph databases) are focused primarily on solving for massive scalability. While that's an important problem to solve, it's not one that most of us will ever face. MySQL is very scalable and can handle the needs of most services rather well.
Still, if a NoSQL database can handle all of your needs as well as (or perhaps even better than) your old RDBMS, should you not go ahead and move to the newer tool? The answer is yes, of course you should. It's only when an RDBMS better serves your needs that you should stick with one. So, with that in mind, here are the reasons one would want to choose an RDBMS over a NoSQL solution:
1 - You require full ACID (Atomicity, Consistency, Isolation, Durability) compliance.
Most NoSQL solutions offer strong data integrity and relatively fast eventual consistency (meaning that, given enough time without change, all replicate sets will share the same data). These BASE (Basically Available Soft state Eventual consistency) characteristics meet the needs of most projects, though maintaining a fully ACID compliant DB structure should be strongly considered if your project meets any of the following considerations:
It is reasonable to expect multiple users to be working on the same data at the same time.
The order in which transactions appear is extremely important
It is unacceptable to ever show a user stale data
There is a significant/direct cost to incomplete transactions (the standard example of a half-complete bank transaction)
2 - You will be engaging in arbitrary and unpredictable queries and joins
If your project involves collecting data that may or may not be related and then asking that data a myriad of different questions, a relational database of normalized data is a strong candidate. You certainly can use a NoSQL database and MapReduce in order to ask similar questions, but an RDBMS may be your best answer.
For example, let's take a database containing movies, theatres, and screen times. Let's now say I want to find all PG-13 rated movies playing in theatres this evening (anytime after, say, 5pm). With a relational model and normalized data, one could simply select from movies where rating is PG-13, join screens where time is later than 5pm and before midnight today. I could accomplish this same query in a document DB (searching screens for unique movie ids in the time period and then querying against movies for which of those met the PG-13 rating requirement, for example), but the operation is more complex.
Continuing the above example, lets add another facet to the database: cast members. Now let's say that I want to find all PG-13 rated movies playing in theatres this evening containing any actor or actress I have marked as a 'favorite'. Again, the RDBMS has no trouble executing this query. A NoSQL DB is likely to struggle a bit more unless such query was predicted from the start. Even then the NoSQL DB will require some data duplication and therefore increase the chances of a failure in data integrity (especially with data that changes often, such as daily screen times).
It must be acknowledged that NoSQL databases do generally ask one to think of their data structures and queries differently (for instance, it could be argued in the first example that all movies could list all of their screen times in a field). That said, sometimes data is best organized in a relational manner (listing all screen times for all 4000 theatres playing The Avengers on opening weekend into a single document object is not my idea of optimal, especially as these screen times would have to be replicated for each theatre in the theatre objects). It can be argued that analytics is an area in which relational organization is especially important.
3 - Referential Integrity
If I need to ensure the referential integrity of my data, an RDBMS is almost certainly the way to go. Looking at the above example, if I were to remove a theatre from my data set a properly configured RDBMS would remove all references to screen times for that theatre. A NoSQL DB with screen times stored in the movie object, however, would not and would run the risk of reporting bad data to the user without myself taking action at the application level to call for subsequent data-cleaning operations through the rest of the data set. These data cleaning operations can be done, but the lack of built-in referential integrity leaves more room for human error and resulting bad data.
Final Conclusion
If the structure of the data object requested by a user is not predictable and relies upon being built through a myriad of possible relations of one data piece to another, an RDBMS should be your tool of choice. The RDBMS will provide you with an easy way to build the requested object and the referential integrity needed to ensure that object is valid at the time it is called.
Questions? Comments? Challenges? Please let me know. I am always open to new ideas, different perspectives, and more learning.













