Ultimate face-off between SQL and NoSQL :
Tags: database, mongodb, mysql, sql
Published: 2020-11-23 18:53:23

# __What is SQL :__
* SQL is a standard language which stands for Structured Query Language. SQL is the core of relational database used for structuring and managing the database .

* The structure is completely made of tables with rows and columns and we can later used the query language (data definition language) for retrieving the data .

## __Relationship in SQL :__
1. __one to one :__
single row in table __'A'__ is related to single row in table __'B'__
2. __one to many :__
single row in table __'A'__ is related to many rows in table __'B'__
3. __many to many :__
many rows in table __'A'__ is related to many rows in table __'B'__
4. __self referencing :__
records in table __'A'__ related to the same table itself .

# __What is NoSQl :__
NoSQL, known as not only SQL database , provides a mechanism of storing and retrieval of data and is the next-gen database . It has no specific schema and can be handle huge amount of data .
* More appropriate to use in big data schenario .
* No row and columns here .
* Collections to be table and Document to be the field in the database .
* Since NoSQL have a dynamic schema we can have multiple documents in of same collection . means we can have an employee document with id and name and another could id and address .
* No specific schema .
* No Query language . 
* No or a few relationship .

## __SQL vs NoSQL :__
* Type of database 
* Schema 
* Database categories
* Complex Queries
* Hierarchical data storage
* scalability
* Language 
* Online processing 
* Base properties
* External support

## __Type of database :__
* SQL is a relational database organizes structured data fields into defined columns .
* NoSQL uses distributed database Data is model in the form of collection hence there is no collection . We can achieve relationship through pointers .

## __Schema :__
* SQL has a predefined schema .
* NoSQL has a dynamic schema 
where you have to only define which document you want to store in which collection .

## __Database Categories :__
* __SQL__
It is having a Table based databases structure ,Where each data is stored in the form of rows and columns .

* __NoSQL__
Four category of database :
1. Document Database :
pairs each key with the complex data knows as documents .  
2. Key Value Stores :
single item in database is stored as the attribute name or a key with the value
3. Graph Stores :
Used to store network informations such as social networks
4. Wide Column Stores
Cassendra stores columns of data together instead of rows

## __Complex QUERIES :__
* SQL is good for Complex queries As the schema is structure if you want to define nested queries then we can achieve that with proper table and column name .

* NoSQL Queries are not as powerful as SQL query language the language here  is defined by the  database schema .

## __Hierarchical data storage :__
* __SQL__ is not a best fit as it usages tables to store data .
As the number of table increase complexity of relating them is also increases . 

* where as in case of __NoSQL__ it is fits better as it uses key-value pair way of storing data similar to JSON data .

## __Scalability :__
* __SQL__ can scale our database in a Vertical manner where we have to add new resources to our infrastructure . 
* __NoSQl__ can scale in Horizontaly .

## __Language :__
* SQL has it's own language called __SQL__ 
* __NoSQL__ vary from database to database .

## __Online Processing :__
* __SQL__ Best fits for heavy type applications . Mainly used for OLTP(online transactional processing) .
Large number of short online transactions insert update and delete
fast query processing , maintain data integrity  , multi access environment and effectiveness measured by number of transactions per second . Data is stored in 3nf structure .

* __NoSQL__ can be Used for Transactional type applications , But it is not safe for high load applications .
Widely used by data mining data mining techniques in the OLAP there is aggregated historical data stored in multidimensional schema usually the star schema . 
Mainly used for OLAP(online analytic application processing)

## __BASE Properties :__
* SQL follows acid properties 
_A_ Atomicity ensures transactions are completely done or fail. 
_C_ Consistency ensures transaction never leave it's current state .
_I_ Isolation mainly handles concurrency control .
_D_ Durability make sure that in case of any power loss or hard shut down our database integrity should not change . 

* NoSQL Follows CAP theorem

_C_ consistency
_A_ Availability
_P_ Partition tolerance

* Out of these three we can use two only .
__consistency__ ensures reader reads most recent data .

__Availability__ ensures that the every transaction either success or fail .

__Partition tolerance__ ensures how our system handles arbitrary message loss or failure due to hard shutdown or may be any network drop or power loss .

## __External support :__
* SQL
SQL is so robust and community support is good . 

* NoSQL
we have to relay upon community vendors .

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/nmj3gjqhi8r70o33gsky.jpg)

Let's compare between MySQL and MongoDB as they both are famous in current market 

# __MySQL :__ 
It is an open source relational database management system that work on many platforms . It supports many storage engine and packed by oracle .
## __Features :__
* High Availability
* High Performance
* Secure data protection
* Comprehensive application development 
* scalability and flexibility
* Open source
* Robust transactional support
* Ease of Management

# __MongoDB :__ 
It is a non-relational database which stores document in a binary representation . This kind of database is used for big data purposes and can store related information together for fast queries and usages JSON .
## __Features :__
* indexing : Index support is for improving search queries .
* Replication : distribute the data among different machines .
* ad-hoc Queries : support ad-hoc queries . 
* Schema less : No Schema is present like SQL
* Sharding : It can implement sharding in case of large datasets .

## __Key Features :__

```
         MySQL                    |        MongoDB 
*Triggers and SSL support .       | * Auto - sharding  
* Provides text searching and     | * Comprehensive secondary 
indexing .                        | indexes .
* Query caching .                 | * In-memory speed .  
* Integrated replication support  | * Native Replication .
* Different storage engine        | * Embedded data models support  
support .                         |
```

## __Usages :__

```
         MySQL                    |        MongoDB 
Best fit for data with tables     | Best fit for unstructured data 
and rows .                        |
works better for small datasets . | Works better for large dataset
frequent updates .                | High write loads .
strong dependency on multi-row    | High availability in unstable  
transactions .                    | environment .
Modify large volume of records .  | Data is location based .
                                  |
```