DEV Community

Cover image for Ultimate face-off between SQL and NoSQL :
Rahul Kumar
Rahul Kumar

Posted on

Ultimate face-off between SQL and NoSQL :

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 :

  • Document Database :
    pairs each key with the complex data knows as documents .

  • Key Value Stores :
    single item in database is stored as the attribute name or a key with the value

  • Graph Stores :
    Used to store network informations such as social networks

  • 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

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 .                         |
Enter fullscreen mode Exit fullscreen mode

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 .
                                  |
Enter fullscreen mode Exit fullscreen mode

Top comments (3)

Collapse
 
margo_hdb profile image
Margo McCabe

Nice overview! Next time you might have to add NewSQL to your database categories 😁

Collapse
 
rahulku48837211 profile image
Rahul Kumar

definitely πŸ”₯πŸ”₯πŸ”₯ i have read the blog and excited to use it .

Collapse
 
margo_hdb profile image
Margo McCabe

very cool!