NoSQL versus SQL

Already this post has started out like many other posts comparing NoSQL to SQL, pitted against each other.  So let’s change that.  The new title of this post is: NoSQL and SQL!

What is NoSQL?

There are two types of NoSQL:

  1. The original NoSQL, which has been around for about two decades and is a relational database, does not use the Structured Query Language (SQL).
  2. The recycled NoSQL, which comes in a variety of flavors, vendors, and either uses SQL, does not use SQL, can use SQL but does not prefer SQL, is relational or isn’t rational, and so on.  It’s new technologies borrowing an already taken moniker.  A better name might be just calling each technology what it is instead of trying to lump them together, i.e. MongoDB, Cassandra, Riak, etc.

They are two different types of tools and one or the other or both can be used in the same environment to perform different tasks.  Let’s look at storing data to highlight a major difference.

Data Design in SQL and NoSQL

A SQL database, such as MySQL or MS SQL, traditionally stores information in tables, rows, and columns you determine during the design phase.  You end up with small, “virtual spreadsheets” that are inter-related to each other through unique identifiers.  The data going into the spreadsheet must fit the constraints of the design of the column the data is going into.  A person’s last name can’t be inserted into an Integer-type field unless that person’s last name happens to be a number.  This is great from a data mining perspective where consistent and precise information is needed.  Your bank’s software will run off of this, and probably Oracle or Microsoft.

Data stored for a person in this type of database usually looks something like:

ID first_name last_name
1 Bill Murray
2 Dan Akroyd

In NoSQL, there normally isn’t this “virtual spreadsheet” to which your data needs to fit into.  Instead, data is generally manipulated and stored in a standardized format, such as JSON or XML.  This is great if your data requirements are changing frequently and/or you want the data to be easily usable by a broad range of applications.  Your social media accounts such as Facebook uses HBase and compliments it with Presto to perform data analytical queries.

In a document-based NoSQL database, data might be stored as:

{
id: 1,
fname: "Bill",
lname, "Murray"
}

But what if the data stored needs to change, for example, adding the middle initial?

In SQL, you would have to create a new column on the table and update any stored procedures, triggers, web application executions, etc. before you could start using the new table.

In NoSQL, you simply change your JSON to:

{
id: 1,
fname: "Bill",
mi: "A"
lname, "Murray"
}

That seems too easy.  Why isn’t everyone doing this?  Many companies are doing this and it really isn’t that easy.  Before you use NoSQL with multiple developers, there still needs to be a defined schema or data structure that needs to be strictly enforced.  There is nothing worse than lost data because it was overwritten by newer and incorrect data.

At the end of the day, developers have been storing JSON and XML data in traditional databases for as long as JSON and XML have existed as a way to get around making changes to the databases’ design and improving data portability.  It was only a matter of time before that method was extracted into a system of its own.

When to Use SQL and When to Use NoSQL

Like any other technology, gather your requirements, use cases, and approach the project with a platform-agnostic mindset.  Even if I dislike Company A, if their Product A is fits the requirements better than Product B, I’m going with Company A.

If you are supporting a high-performance, high-availability web application, consider NoSQL technologies.

If you are building financial transaction software that requires rigid data consistency, reporting, and auditing, consider a solution like Oracle or Microsoft.

If you are considering building a financial transaction software with a huge web-based user population, consider a SQL back-end with a NoSQL front-end, which is what Chase, Bank of America, and other global entities have done with MongoDB for their customer relationship management (CRM) aspect of their companies.

Why Can’t I Use NoSQL for Financial Data

You can.  I’m not telling you how to design your solution, by any means.  However, There are very few NoSQL solutions that would/can pass an ACID test.  Even tech giants like Facebook and Twitter still use a SQL database to store vital data and provide reporting.  Companies like Chase and Bank of America, still use SQL farms for the critical data.  That shouldn’t deter you from designing your solution.

Quick ACID refresher:

A – Atomicity – Either perform the transaction or don’t, no middle ground.
C – Consistency – Only valid data be written and committed.
I – Isolation – Transactions occurring at the same time are performed independent of each other.
D – Durability – Ability to recover working data from a failure or disaster.

Without these in place, applications built using NoSQL would likely fail State and Federal audits.

Want to Learn More about NoSQL?

There are many different flavors of NoSQL but only four (4) main types of NoSQL databases.

  1. Columnar – Just as it sounds, reads/writes by column instead of row.
  2. Document – This is the JSON example I used above, and most frequently used for web applications.
  3. Graph – Uses edges, nodes, and properties to directly store the relationship between records.
  4. In-Memory Key-Value Stores – Retrieves commonly requested data from memory based on a key that correlates to that data.

I’ve provided links above where you can test them for free on Amazon’s Web Services.  Amazon also provides free training.  You can also download community versions of popular NoSQL software and install them in VMware or VirtualBox on your own device.

Quick Summary

SQL and NoSQL are non-competing technologies useful for different purposes.  You may have a need for one, the other, or both.  NoSQL may improve the performance, scalability, or availability of your application.  Test your options, know your requirements, and choose the best tool for the task.

Downloads

These or any other links on this page are provided as a courtesy and are not intended as an endorsement.  There are many flavors of SQL and NoSQL.

AWS Command Line Interface for DynamoDB

Apache Cassandra

Apache HBase

Microsoft SQL Server 2016

Microsoft SQL Server 2016 on Linux

MongoDB

MySQL

Oracle