SQL vs NoSQL | Difference between SQL & NoSQL | SQL Vs NoSQL Tutorial | SQL, NoSQL system design

 SQL

 1. Structure

        Table,row,column

        PreDetermined Schema

                Create table with all the defined datatypes and size.

                Then you can use the table

        Relations

            1:1,1:M,M:M

2. Nature

         Centralized data-all tables in the same database server

3. Scalability

        Vertical 

                Increase the RAM,Storage

        Horizontal -> NOT good for horizontal scaling

                Sharding

                        based on row and column - Not well supported

                         Some table data cannot be stored in different database server due to centrallized data 

4. Property: ACID

        Data integrity

        Consistency

        Isolation

        Durability


NoSQL

    Non Relational

    No SQL

    Not Only SQL

 1. Structure

        Unstructured data

            Key Value DB

                      {Key:value}

                      Search based on Key only, value is opaque can be string,integer,json but cannot be quered

                      <DynamoDB>   

            Document DB

                        JSON,XML

                        both Key and Value can be quered.

                        {Key:value}

                       <MongoDB>

            Column DB

                    Key Value

                              Key ->{Column:value}

            Graph DB

                    {Node, Edge(relationship)}

 2. Nature

         No Centralized - data can be distributed database server

3. Scalability

        Vertical 

                Increase the RAM, Storage

        Horizontal -> Good for horizontal scaling, as we can store data in node 

4. Property -BASE

        Basically Available-> Highly available DB due to replication.

        Safe State->State of data can be changed, as it is distributed, and will sync

        Eventually Consistency->Query then you may get slate data, but eventually you will get latest data after retry.


When to use which one?

Flexible Query Functionality

NoSQL does not support complex query

Basic Search use NoSQL

If Data is relational then use SQL

Data Integrity use SQL- (cannot lose a single transaction/consistency) -example banking, financial.

NoSQL->Handle big data, so losing data is of no impact.

High Availability, High Performance with some inconsistence then go for NoSQL because of distributed nature.



Comments

Post a Comment

Popular posts from this blog

Microservice Pattern: SAGA

Image
  Context You are developing a server-side enterprise application. It must support a variety of different clients including desktop browsers, mobile browsers and native mobile applications. The application might also expose an API for 3rd parties to consume. It might also integrate with other applications via either web services or a message broker. The application handles requests (HTTP requests and messages) by executing business logic; accessing a database; exchanging messages with other systems; and returning a HTML/JSON/XML response. There are logical components corresponding to different functional areas of the application. Problem What’s the application’s deployment architecture? Forces There is a team of developers working on the application New team members must quickly become productive The application must be easy to understand and modify You want to practice continuous deployment of the application You must run multiple instances of the application on multiple machines ...
Read more

Microservice Pattern: Database per service Context

Image
Let’s imagine you are developing an online store application using the   Microservice architecture pattern . Most services need to persist data in some kind of database. For example, the   Order Service   stores information about orders and the   Customer Service   stores information about customers. Problem What’s the database architecture in a microservices application? Forces Services must be loosely coupled so that they can be developed, deployed and scaled independently Some business transactions must enforce invariants that span multiple services. For example, the  Place Order  use case must verify that a new Order will not exceed the customer’s credit limit. Other business transactions, must update data owned by multiple services. Some business transactions need to query data that is owned by multiple services. For example, the  View Available Credit  use must query the Customer to find the  creditLimit  and Orders to calcula...
Read more