SQL vs NoSQL Databases
A theoretical approach for Software Engineering Interviews
My Software Engineering Bootcamp only taught us Relational Database Management Systems (RDBMs), and my head exploded when I found out there were different types of databases out there—document-based, graphs, key/value, and more!
I still don’t know how to use any of them, but I’m getting a lot of mileage in interviews by being even just being able to acknowledge and discuss the world beyond SQL.
So to start, what is a relational database? If you learned how to build a SQL database based on a table with rows and columns, that’s a relational database. Put it on your resume: “relational database management!” But…hold on to your butts…data can be stored in DIFFERENT WAYS! And typically it’s broken into two camps—SQL vs NoSQL.
SQL
To review, SQL databases have structured tables with rows and columns. Popular relational databases include MySQL, Oracle, MS SQL, Postgres, and MariaDB.
Pros: They allow you to easily establish joins with foreign keys and make for powerful SQL querying. Plus they are incredibly reliable.
Cons: You have to decide in advance how you want to organize your data for those tables and if you do decide to restructure, you will likely have to go offline to drop and remigrate your db. And when you start partitioning your database as it scales, querying and joining can get tricky.
NoSQL
NoSQL databases can have a bit more of an open structure. In fact, they can have a lot of different, open, structures.
Key-Value: Data is stored in key/value pairs. Popular dbs here include Redis, Voldemort, and DynamoDB.
Document: Data is stored in documents, documents are stored in collections. Each document can have different structures! CouchDB and MongoDB are popular examples.
Wide-Column: Here we have column families instead of tables which serve more as containers. These are used for analyzing large datasets: Cassandra, HBase.
Graph Database: Data is stored in nodes, properties, and lines: Neo4J and InfiniteGraph. Imagine here that data best displayed on a graph can be stored in a graph!
Pros: These are more dynamic. You don’t have to decide in advance the structures of your data. And they allow for easier horizontal scalability.
Cons: They are less reliable, aren’t as easily queried, and joins are not necessarily as easy to accomplish.
So what is the best?
When it comes to DB technology, there really is no one size fits all solution and a lot of systems use a combination of both. ← You’ll hear this a lot. But here is a great response to an interviewer if they ask:
If your data is static and unchanging, and you need it to be reliable and ACID compliant, go with SQL!
If your data is dynamic and fast paced, if you need to make the most of cloud computing and storage, go noSQL. It allows you to scale most easily horizontally for rapid development.
If you really wanna rock some socks, study up on the CAP or PACELC Theorems, but I find that even knowing the above goes a long, long way.
A break down of the differences in a few categories:
Go get ‘em!