MongoDB or SQL? When to apply the right technology
As technology evolves and more data is created each day from many different sources, we find ourselves trying to come up with better solutions to manage all of the information (or digital gold as many like to call it) in the best way possible. People involved with software engineering and development should be familiar with databases—collections of structured information—where we can store, retrieve, and manipulate data. It is “mandatory” knowledge for those working in the IT field even if they are not directly involved with development.
This article is meant to analyze the way we choose a database by evaluating the most common option, a SQL database, and comparing it to a NoSQL database such as MongoDB. The idea here is to display the strengths and weaknesses of both options and provide a clear view of when one can be a better option than the other given a range of factors.
The traditional SQL approach
SQL (structured query language) is a language used to interact with relational database systems. This type of database is composed of tables where each table has columns that represent the type of data to be stored, like user_name, user_email, and rows that include the stored data, like John, firstname.lastname@example.org.
In a SQL database, the data manipulations are made by queries like “SELECT id, name FROM user,” where SELECT and FROM represent which pieces of data we are looking for and which table we want that information from. There are many more types of queries that can be used to handle the data, such as joining two or more tables, updating information, deleting information, and many other operations.
The tables in a database can have different types of relations among themselves where one table can be responsible for storing basic user information and another table can store the contact information for a specific user. This relationship will be made by an ID column in one of the tables to represent the connection between both tables, like in the image below where user_id in the table user_info is a reference (also known as a foreign key) to id in the table user:
The relations between tables can become more complex depending on the number of tables in the database and how they relate to each other. The amount of data in a SQL database can become a hard (or impossible) problem to handle after a certain point, but we will discuss this later. For now, let us move to MongoDB.
The simplicity of MongoDB
MongoDB is a NoSQL database (the most popular one) which means that it doesn’t use SQL to handle data. Mongo also doesn’t use tables. Instead, it uses a structure known as collections to store the data in the form of a document—the same way you would write information on a sheet of paper but with the same structure as a JSON object like in the image below.
Unlike a SQL database, we don’t require two documents for user and user_info. In MongoDB, we can have both in the same document. Although Mongo has a certain degree of relations between documents, it is not as common (or required) as in SQL. Usually, you will have most of the information in one document depending on the type of data you are storing. The documents don’t have a fixed structure. The fields will only exist if you store data inside them. For example, inside the info field, we have email and phone. If the user doesn’t have a phone and/or email, the field will not be created (it can easily be created later by updating the document), unlike SQL where the field remains there even if you don’t have data to store.
SQL vs MongoDB: Strengths and Weaknesses
As in life, every story has at least two sides and the same can be said about technology, so let us compare SQL and MongoDB by exploring their strengths and weaknesses.
|Follows a fixed structure for the table and can have lots of gaps in many columns.
|Doesn’t have a fixed structure and documents can have different fields in the same collection.
|Each table handles different types of information by creating relations between two or more tables in order to find all the required data, which makes it easy to update since every table has unique data.
|Relations are not common since all the data can be stored together. It is a good approach if you don’t have to update the same information in many places.
|It becomes slow when dealing with lots of thousands of queries per second.
|It is faster for simple writing and reading queries.
|Horizontal scaling is hard (or impossible) since it is hard to break tables. Vertical scaling is possible.
|Can be easily scaled both horizontally and vertically.
|It demands more time to master and has lots of tools to help process data but it can be underused when dealing with simpler systems.
|It is easy to pick-up but can be a liability in systems that require constant updates for the same data in different collections.
Having analyzed the comparison above, it is easy to think that SQL is a good choice for more demanding systems and MongoDB is good for less complex operations. There are even cases where both are used in the same system. A good way to understand how both options can be used is by experimenting. It is easy to use familiar technology, but it is always good to explore a different view and experiment with it, especially if you are a developer. With that said, try to develop the same system with both options to see how they behave. This will give you more options for handling problems in the future.
As we know databases are becoming more important every day as information becomes more precious and having the right tools to deal with the data can make a huge difference. Try to explore both options, read the documentation, and experiment. Knowledge expands one’s horizons.
About the Author
I am a software engineer passionate about technology, always challenging myself to evolve and achieve my goals. I am always interested in learning new things and sharing my knowledge with my colleagues and friends.
– – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – –