Why do we need NoSQL over SQL and how do we choose the best database for the project?

In this article we will learn about databases RDBMS and NoSQL.

What is a Database?

Database is Structured, provides Random access that will help us to search data quickly based on index, having low latency and provides ACID properties.


source: Google images

ACID properties:

  1. Atomicity : The data will be transfer either full or none will be transferred. Example: When you transfer money online using netbanking , either full amount should be transferred or none should be transferred.
  2. Consistency : Data should be consistent.
  3. Isolation : If multiple people are working in same bank / doing transfers at the same time, the there should be a sequencing/locking mechanism
  4. Durability : Whenever there is a database failure, the system should be up and running to save the data.

Why do the companies want NoSQL over SQL?

NoSQL databases offer many benefits over relational databases. NoSQL databases have flexible data models, scale horizontally, have incredibly fast queries, and are easy for developers to work with. NoSQL databases typically have very flexible schemas.

NoSQL characteristics:

A distributed system is a network that stores data on more than one node (physical or virtual machines) at the same time. Because all cloud applications are distributed systems, it’s essential to understand the CAP theorem when designing a cloud app so that you can choose a data management system that delivers the characteristics your application needs most.

CAP theorem explained:

  • Consistency  -- Each node will hold the latest value. It can't give the old value.
    • For example: In banking, we can withdraw / transfer money based on latest balance. If there is no clear latest information, it will deny the transaction.
  • Availability  -- System should always give response. There is no guarantee that the value is latest.
  • Partitions to tolerance -- system will continue to operate even when there is a network partition / failure
                                                    
                                                                                     source: https://data-science-blog.com/en/blog/tag/nosql/

CAP theorem says that out of these 3 we can get only 2.We can have the system that gives all 3 components. In case of distributed computing, we should have partitions to tolerance. Hence, distributed systems fall into 2 categories 

  • CA Database (Consistency & Availability):

    • A CA database delivers consistency and availability across all nodes. It can’t do this if there is a partition between any two nodes in the system, however, and therefore can’t deliver fault tolerance. In a distributed system, partitions can’t be avoided. So, while we can discuss a CA distributed database in theory, for all practical purposes, a CA distributed database can’t exist.
    • However, this doesn’t mean you can’t have a CA database for your distributed application if you need one. Many relational databases, such as PostgreSQL, deliver consistency and availability and can be deployed to multiple nodes using replication.

  • AP Database.(Availability & Partitions to tolerance) :

    • An AP database delivers availability and partition tolerance at the expense of consistency. When a partition occurs, all nodes remain available but those at the wrong end of a partition might return an older version of data than others. (When the partition is resolved, the AP databases typically resync the nodes to repair all inconsistencies in the system.)
    • Typically NoSQL databases like Cassandra and DynamoDB.

  • CP Database - Consistency and Partitions to tolerance :

    • A CP database delivers consistency and partition tolerance at the expense of availability. When a partition occurs between any two nodes, the system has to shut down the non-consistent node (i.e., make it unavailable) until the partition is resolved.
    • Typically NoSQL databases like HBase and MongoDB

Based on our requirement, we need decide which combination we need for the project .

  • When do we choose AP over CP ?:The systems should be available immediately. We want some results though they are not latest.
    • Ex: Booking a hotel. The system is not able to verify the latest price due to network partition. But the hotel price is available. In Travel industry,  if there is an error, then the customer will go to some other portals to book hotels. So Availability is the main thing here than consistency. The travel portals like gobibo, booking.com prefer availability over consistency.
  • When do we choose CP over AP ? : Preferred when we need latest results.
    • For example, in chats like WhatsApp, the system can wait until the network is connected and deliver the latest message.
    • Also in banking applications, the system can give error but it should not work on wrong balance.
source: https://www.ibm.com/uk-en/cloud/learn/cap-theorem

Comments

Post a Comment

Popular posts from this blog

How do you build a data warehouse in SQL server?

Building a data warehouse in SQL server is a complex process that requires a deep understanding of SQL Server and data warehousing best practices. It involves several steps, including planning, designing, and implementing the database schema, as well as loading and transforming the data. Here are the high-level steps you can follow to build a data warehouse in SQL Server: ·          Identify the business requirements : Before you start building a data warehouse, you need to identify the business requirements, such as what data is needed, who will use the data, and how the data will be analysed. ·         Plan the data warehouse schema: Once you have identified the business requirements, you need to plan the data warehouse schema. This involves designing the tables, relationships, and indexes that will be used to store the data. ·         Create the database: After plann...
Read more

When to choose data warehouse over database?

  The decision between a database and a data warehouse will depend on your specific needs and use case. Deciding between a database and a data warehouse largely depends on the specific needs of your organization and the type of data you're working with. Some of the factors for consideration are: Ø  Data Volume : When you have large volume of data (e.g., terabytes or more), then better to go for a data warehouse as data warehouses are optimized for handling large amounts of data. Databases are designed to handle smaller amounts of data. Ø  Data Complexity : If you are working with complex data that requires advanced querying and analysis, then, a data warehouse will be a better choice. Data warehouses can handle complex queries and data modelling. Databases may not be equipped to handle the same level of complexity. Ø  Data Integration : If you need to integrate data from multiple sources, a data warehouse may be the better choice. Data warehouses are designed to hand...
Read more