SQL vs. NoSQL

Published on Author craigzearfossLeave a comment

NoSQL is an alternative to SQL, not a replacement.

SQL databases include MySQL, PostgreSQL, SQLite and Oracle.

NoSQL databases include MongoDB, CouchDB, Redis and Cassandra.

Your requirements should determine the database type — not the language.

SQL NoSQL
Design is rigid Design is flexible
Schema must be defined Data can be added anywhere
Normalized data Denormalized data
Joins No joins
Enforces data integrity No data integrity options
Transactions No transactions

 

SQL databases
  • Provide a store of related data tables.
  • Design is rigid.
  • SQL tables create a strict data template, so it’s difficult to make mistakes.
  • Data schema must be designed and implemented before any business logic can be developed to manipulate data.
    • primary keys — unique identifiers which apply to a single record
    • indexes — commonly queried fields indexed to aid quick searching
    • relationships — logical links between data fields
    • functionality such as triggers and stored procedures.
  • Large changes can be complicated.
  • Normalization of data minimizes data redundancy.
  • JOIN clause allows you to obtain related data in multiple tables using a single SQL statement.
  • Enforce data integrity rules using foreign key constraints.
  • Can wrap two or more updates in transactions.
  • Lightweight declarative language.
  • Scaling can be tricky.

 

NoSQL databases
  • Store JSON-like field-value pair documents.
  • Similar documents can be stored in a collection, which is analogous to an SQL table.
  • More flexible and forgiving, but being able to store any data anywhere can lead to consistency issues.
  • Data can be added anywhere, at any time. (No need to specify a document design or even a collection up-front.)
  • May be more suited to projects where the initial data requirements are difficult to ascertain.
  • Denormalized data can result in data redundancy. However, you can use normalization techniques.
  • NoSQL has no equivalent of JOIN. (This is one reason denormalization is often essential.)
  • Modifications of single documents is atomic, but no transaction equivalent for updating multiple documents.
  • Uses JavaScripty-looking queries with JSON-like arguments.
  • Simpler data models generally make scaling easier.

 

Projects where SQL is ideal:
  • logical related discrete data requirements which can be identified up-front
  • data integrity is essential
  • standards-based proven technology with good developer experience and support
Projects where NoSQL is ideal:
  • unrelated, indeterminate or evolving data requirements
  • simpler or looser project objectives, able to start coding immediately
  • speed and scalability is imperative

 

Sources:

Leave a Reply

Your email address will not be published. Required fields are marked *