Understanding and Comparing Relational and NoSQL Database Management Systems
Tags: sql, nosql, beginners, database
Published: 2023-10-01 21:24:59

## Understanding
Before we dive into comparing these database management systems. Let's start from the beginning to ensure we understand what a database is, how database management systems (DBMS) are used to interact with and manage databases, and the features of relational and noSQL DBMSes.

**What is a database?**
This is pretty intuitive and many may be able to make an educated guess on this definition. A database is a collection of related data that is populated with a purpose and includes relevant information pertaining to that purpose. 

**What is a Database Management System?**
If a database is just a collection of related and relevant data, then a DBMS must be the way you manage that data so it can be useful for your purpose. More specifically, a DBMS allows you to define the database's schema which is the description of how data is stored including the data's type, structure, and/or constraints. Additionally, the DBMS is used to construct, store, update, query, share, and control access to the data.

As you can see, the DBMS gives you the functionality to make use of your database, so understanding the features of each can help you to better manage your data with the system you are using or plan to use.

**Relational Database Management Systems**
Simply put, relational database management systems(RDBMS) store data in tables and primarily use Structured Query Language (SQL) to access the database. Some modern RDBMSes are mySQL, Microsoft SQL, and Oracle. An RDBMS can have any number of tables, but the relationship of the columns and rows is consistent throughout. Columns represent specific information about every record, and the rows represent each record. Additionally, all tables include a column of a unique identifier called the primary key. This is used to identify the table as well as link various tables to one another. When one table's primary key is used in another table, it is referred to as a foreign key. Let's take a look at an example. 

Say you wanted to store data using a relational database management system to hold information about all of your pets. Let's make the first table hold general information about each pet. The second, linked table will hold nickname data for each pet.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vo92qny5qldcu3uwsyqd.png)
As you can see in the tables above, both have a primary key that give each record in the table a unique identifier. The primary key in the Pets table is Pets ID which is used as a foreign key in the Nicknames table to link the nicknames of each pet to the general information about that pet. By having access to both tables, you would know that Georgia's nicknames are Sausage and Angel baby. 

This kind of structure is helpful in reducing any duplication of information which in turn saves storage--an important point in the early uses of RDBMSes. Now, let's talk about the non-tabular systems, also referred to as noSQL or 'not only SQL'

**NoSQL Database Management Systems**
These management systems store data in a variety of data structures. The main ones include:

- document-based: stores data in documents similar to JSON objects where each document contains pairs of fields and values.

- key-value: stores data in key value pairs

- wide-column: stores data in tables with dynamic columns

- graph: stores data in nodes and edges

Although each has their own unique features, in general noSQL databases include flexible data models and horizontal scaling. Having flexible data models allows developers to be agile and quickly change requirements. There are no strict schemas that must be followed which allows developers to keep up with the needs of today's fast-paced businesses. Horizontal scaling means you do not need to scale up to a larger server when you have reached capacity. Instead, you can spread out over cheaper commodity servers when necessary.

Now that we know a bit more about what noSQL DBMSes are, let's take a look at data from the example above refactored using a document-based noSQL DBMS.
  
`{
  id: 0o1,
  name:'Georgia'
  breed:'Basset Hound Mix'
  birthday: 3-24-2015
  favNapSpot:'in front of fireplace'
  nicknames: ['Angel baby', 'Sausage']
}

{
  id: 0o2
  name: 'Reggie'
  breed: 'Pit Bull Mix'
  birthday: 7-4-2016
  favNapSpot: 'couch by Mom'
  nicknames: ['Mr. Reggie', 'Brother']
}`
Unlike the RDBMS example, where you would need to join both tables to have access to all the information about a pet, with this example, you only need to access one of the documents to have all of the data on a pet.

Now that we have a deeper understanding of all things database managements systems, let's compare two popular systems from each type.

## Comparing
**Relational vs noSQL**
The main advantage of RDBMSes is that multiple data elements can be accessed at the same time. This allows for multi-record ACID (atomicity, consistency, isolation, durability) transactions which provides a high level of data consistency. On the other hand, it can be very ridged because you must have predefined schemas that set the structure of your data.This means there is a lot of pre-planning that must go into set up and changing that structure is complicated and cumbersome. 

In contrast to these advantages and disadvantages, noSQL DBMSes allows for a lot of flexibility when it comes to data structures. Documents/data elements do not need a pre-defined schema and each document can have a different structure. Although this flexibility is necessary for the evolving needs of fast-paced industries, some argue noSQL DBMSes offer less security and data integrity. 

Scalability is another main difference between the two types. RDBMSes have vertical scalability meaning that you must scale up to a larger server when your have reached your max capacity. Although this can be viewed as a disadvantage because getting a larger server is costly, storage is more conservative in RDBMSes because they were invented much closer to the advent of the internet and computers when storage was costly. On the other hand, noSQL DBMSes have horizontal scalability meaning you can spread over many servers. This is advantageous for many modern businesses that have ever growing data storage needs.

## Conclusion
After reading this article, I hope you come away with a better understanding of the database basics. Having this foundational knowledge before diving into more complicated database management and administration is important. We can see that both types of management systems have their pro and cons. RDBMSes make it easy to ensure the security and integrity of large amounts of data and have an intuitive data structure that is easy to understand; whereas, NoSQL DBMSes are better when availability and flexibility of your data is of higher priority.

## Sources
What is a database? and What is a DBMS?
https://spots.augusta.edu/caubert/db/ln/CSCI_3410_lecture_notes.pdf
Relational DBMS
https://www.techtarget.com/searchdatamanagement/definition/RDBMS-relational-database-management-system
NoSQL
https://www.mongodb.com/nosql-explained
https://intellipaat.com/blog/what-is-no-sql/
Comparing
https://www.geeksforgeeks.org/difference-between-sql-and-nosql/?ref=lbp