Choosing the right method to store your data can be a critical decision early in the development process. When deciding on which type of database to choose for your project it really comes down to the data structure you wish to use rather than the specific product or provider. In this blog post, we are going to look at relational and non-relational databases and explore the differences and use cases for both.
So, to clarify things upfront, Structured Query Language, or SQL is the common language used across multiple different underlying relational database management systems (RDBMS).
A relational database system consists of either one or multiple tables. Tables are simply structured (and type constrained) storage for your data and consist of rows of records that can have one or many columns of data.
An example of records in a relational database table
When accessing SQL databases, you use what is known as CRUD operations (create, read, update, and delete)
A simple SQL read query looks something like this:
SELECT [Name], [Age] FROM CLIENTS WHERE [Gender] = ‘Male’
This statement is querying the [CLIENTS] table for any records that have a [Gender] value of ‘Male’. Then return the name and age of each client record that matches.
Some of the most well-known RDBMS are
The real selling point of SQL systems is the fact that you can build relational connections between tables and then run a variety of queries against it to return multiple different datasets based on your requirements.
SQL databases are typically vertically scaled, meaning the database instance is located on one server and to scale up you keep adding/upgrading that server. This typically means your storage is also concentrated, and one location contains your entire database. It is possible to scale horizontally in SQL, this usually involves copying your database across multiple different machines, then requests can be load-balanced across these instances.
SQL database solutions are great if you have a clear understanding from the inception of the project of your data storage requirements and how your data is to be organised – schema.
As the name hints, NoSQL databases are used for data that is non-relational. The term NoSQL tends to cover quite a few different implementations such as
An example of a non-relational database document in MongoDB
The common link between all these implementations is that they use a Key-Value structure for storing records. Therefore, you need to know the key you are looking for beforehand to get at your data.
This varies across vendors and products but generally you have the option of using vendor-specific CRUD queries like SQL or REST APIs to access your data.
In terms of storage, NoSQL uses a hashing function to decide where to store your data. You provide the key, and the result of the hashing function is a value distributed onto one of the multiple nodes. This design makes NoSQL ideally suited for horizontal scaling as you just need to add more partitions/node to scale up.
NoSQL solutions are generally built to be able to scale for high performance, but you sacrifice some of your query flexibility due to the key-value nature of the storage.
This table summarised the details listed above, hopefully, this format makes it easier to decide what makes more sense for your project.
|If the way you want to access your data isn’t defined||When you know how specifically you want to access your data|
|If you want to use flexible queries||When you know your primary keys|
|You need to make use of the relational nature of SQL||If you want to use a nonstandard data model like graphs or documents|
|If you want to constrain the values being written to your database||When you are focused on high performance and scalability|
|When data integrity and consistency is an important deciding factor||When you want to store large sets of unrelated and unstructured data.|
Like most things in life, there is rarely a one size fits all option. This isn’t a scenario where one is the fundamentally better technology. You need to assess your project requirements and make an informed decision of what option provides you with desirable functionality.
As always, I hope you have enjoyed this blog post!