A comparison between relational database and NoSQL

The SQL (Structured Query Language) database is the primary way to store data over the past 40 years. The late 1990s with the rise of Web applications and open-source databases such as MySQL, PostgreSQL, and SQLite, the explosion of user growth.

NoSQL databases have been in existence since the 1960s and have gained more attention until the popularity of databases such as MongoDB, CouchDB, Redis and Apache Cassandra.

You can easily find a lot of tutorials on how to use a particular SQL or nosql, but there is little discussion about why you prefer to use one instead of the other. I wish I could fill this void. The differences between them will be described in this article. In subsequent articles, we will determine the best choice based on a typical scenario.

Most of the examples apply to the popular relational database MySQL and MongoDB nosql databases. Other sql/nosql are similar, but there are subtle differences in grammar and character.

The war between SQL and NoSQL

Before we go any further, let's not think about the various points of view ...

Point one: NoSQL will replace SQL

This view is like saying that the ship will replace the car, because the ship is a new technology, it is impossible to happen. SQL and NoSQL have the same goal: to store data. They store data in different ways, which can affect the projects you develop, one that will simplify your development and one that will hinder your development. Despite the current popularity of NoSQL databases, NoSQL is no substitute for SQL-it's just a substitute for SQL.

Opinion two: NoSQL is better/worse than SQL

Some projects may be more appropriate for SQL databases, while some projects may be more appropriate for using NoSQL, and some projects can be used to achieve the desired results well. This article does not support either party, because there is no way to use all of the projects.

Opinion three: There is a clear difference between SQL and NoSQL

This view is not quite right. Some SQL databases also take on the characteristics of NoSQL databases, and vice versa. The boundaries of choosing a database are becoming increasingly blurred, and some new hybrid databases will provide more choices in the near future.

Opinion four: Language or framework determines which database to use

We are accustomed to using some existing frameworks for development, such as:

• Lamp:linux,apache,mysql (SQL), PHP
• Mean:mongodb (NoSQL), express,angular,node.js
• . Net,iis and SQL Server
• Java,apache and Oracle

Because of the many practical, historical and commercial reasons that have led to the development of these frameworks, they are not a rule constraint. You can use MongoDB in your PHP and. NET projects. You can also use MySQL or SQL Service in node. js. You may not be able to find good tutorials and resources when you use the appeal development model, but our development should be determined by the need to use the type of database rather than the database language.

(in other words, don't get asked!) It is possible to choose a niche technology portfolio or to combine SQL and NoSQL, but you will find it very difficult to find experienced developers and related technical support.

Let's take a look at the main differences between them ...

A table in SQL and a document in NoSQL

SQL database provides relational tables to store data. For example, if you are maintaining an online bookstore, the book information should be stored in book the table:

Price

ISBN	title	author	format
9780992461225	Javascript:novice to Ninja	Darren Jones	ebook	29.00
9780994182654	Jump Start Git	Shaumik Daityari	ebook	29.00

Each row is a record of a different book. Such a design is inflexible, and you cannot use the same table to store information about different structures or insert strings where you want to insert numbers.

NoSQL databases use class Josn key-value pairs to store documents, such as:

{    9780992461225,    "Javascript:novice to Ninja",    "Darren Jones",    "ebook",    29.00}

Documents of the same type are stored as a collection (collection), similar to the table structure in a relational database. However, you can store arbitrary data in any document, and NoSQL databases do not compare. For example:

{  9780992461225,  "Javascript:novice to Ninja",  "Darren Jones",  "ebook", 29.00,  "Learn JavaScript from scratch!"     ,  " 5/5 ",  review: [    " A Reader ", Text:" The best JavaScript book I ' ve ever read. " },    "JS Expert", Text: "Recommended to novice and Expert developers alike."  }  ]}

The table structure in SQL has strict data schema constraints, so it is difficult to store data with errors. NoSQL stores data more flexibly and freely, but it can also lead to data inconsistency problems.

SQL Mode VS NoSQL non-modal

In a relational database, you cannot add data to a table or field unless you have defined it in advance. The pattern contains a lot of information:

• PRIMARY key -unique logo Just like ISBN uniquely identifies a record
• Index -usually sets the index field to speed up the search
• relationship -A logical connection between fields
• Design features such as triggers and storage programs

We have to define the data schema before the logical operation of the data. Data patterns can be changed at a later stage, but a large change to the schema will be very complex.

In a NoSQL database, data can be added at any time. You do not need to define documents and collections beforehand. For example, in MongoDB, the following action will book recreate a document in the collection if it was not previously created.

Db.book.insert (  9780994182654,  "Jump Start Git",  "Shaumik Daityari" ,  " ebook ",  29.00);

(MongoDB adds a unique _id to each document in the collection.) If you still want to define the index, you can define it yourself later)

A NoSQL database is better suited to projects that are not able to identify data needs. In other words, don't make trouble for your laziness: Designing a good data storage model at the start of a project can be a problem in the future.

Normalization of SQL language VS. Non-normalization of NoSQL language

Suppose we want to add a publisher's information to the bookstore's database. A publishing house will publish a lot of books, so in the database we create a table publisher :

ID	name	Country	Email
SP001	SitePoint	Australia	[Email protected]

We want to book add a field to the table publisher_id that references the ID in the publisher information:

Price

ISBN	title	author	format		publisher_id
9780992461225	Javascript:novice to Ninja	Darren Jones	ebook	29.00	SP001
9780994182654	Jump Start Git	Shaumik Daityari	ebook	29.00	SP001

Such a design minimizes the redundancy of the data, and we do not need to add all of the publisher's information to each book repeatedly-just to quote it. This technique, called database normalization , has practical significance. We can change the publisher information without modifying book the data.

We can also use normalization techniques in nosql. bookdocuments in the collection:

{  9780992461225,  "Javascript:novice to Ninja",  "Darren Jones",  "ebook",  29.00,  "SP001"}

Referencing publisher a document in the collection

{  "SP001"  "SitePoint",  "Australia",  "[Email Protected] "}

In practice, however, we will not do so. We will be more inclined to choose non-normalized our documentation to repeat the publisher's information for each book

{  9780992461225,  "Javascript:novice to Ninja",  "Darren Jones",  "ebook"  , 29.00,  Publisher: {    "SitePoint",    "Australia" ,    " [Email protected] "  }}

This makes the query faster, but the efficiency of updating the records of the publishing house information becomes significantly lower.

Join operations for SQL Relationships VS NoSQL

The SQL language provides a powerful join operation for queries. We can use a single SQL statement to get related data in multiple tables. For example:

SELECT Book.title, Book.author, Publisher.name  from  Book  Left JOIN  on Publisher.id;

This SQL statement returns the title of all books, the name of the author and the associated publisher.

There is no action in NoSQL that is the same as join, which is very shocking for someone with a SQL language experience. If we use the normalized collection above, we need to remove all the documents from the book collection, retrieve all the publisher documents, and manually connect them in the program. This is one of the reasons why the non-normalization exists.

SQL VS NoSQL Data integrity

Most databases allow you to define the integrity constraints of a database by defining a foreign key. Our database can guarantee:

• Make sure that all the books publisher_id correspond to publisher one entity in the
• If there is one or more of the books in publisher_id the publisher corresponding with the ID, then the Publisher can not be deleted.

Data patterns ensure that these rules are adhered to by the database. A developer or user cannot add, modify, or remove a record if the operation causes data to produce invalid data or a single useless record.

There are no constraint options for data integrity in a NoSQL database. You can store any data that you want to store. Ideally, a single document would be the only source of all the information for the project.

SQL VS NoSQL Transactions

In SQL database, two or more update operations can be executed in conjunction with one transaction (or failure to do so successfully). For example, suppose our book database contains the order and stock tables. When a book is ordered, we want to order add a record in and reduce the number of stock stocks in. If we execute each of the two update operations separately, one succeeds and the other fails---this will result in a database inconsistency. Binding two update operations to a transaction ensures that they either all succeed or all fail.

In a NoSQL database, the update operation for a document is atomic. In other words, if you want to update three values in a document, either the three values are updated successfully or they remain unchanged. However, there is no action for a rain transaction when working with multiple documents. One of the operations in MongoDB is the transaction-like options, but we need to add it manually to the code.

SQL VS NoSQL CRUD (add-and-revise) syntax

Additions and deletions is the basic operation of the database. Essentially:

• SQL is a declarative language. The SQL language is powerful and has become an international universal standard, although most systems have slightly different grammatical nuances.
• NoSQL databases use javascript like the JOSN parameter to query! The basic operations are the same, but nested JOSN will produce complex queries.

Comparison:

SQL	NoSQL
Insert a new book record
INSERT INTO book (ISBN , Title , author)VALUES ( ‘9780992461256‘, ‘Full Stack JavaScript‘, ‘Colin Ihrig & Adam Bretz‘);	db.book.insert({ ISBN: "9780992461256", title: "Full Stack JavaScript", author: "Colin Ihrig & Adam Bretz"});
Update a book record
UPDATE bookSET price = 19.99WHERE ISBN = ‘9780992461256‘	db.book.update( { ISBN: ‘9780992461256‘ }, { $set: { price: 19.99 } });
Return all book titles over $
SELECT title FROM bookWHERE price > 10;	db.book.find( { price: { >: 10 } }, { _id: 0, title: 1 });The second JSON object is known as a projection: It sets which Fields was returned (is _id returned by default So it needs to be unset).
Count the number of SitePoint books
SELECT COUNT(1) FROM bookWHERE publisher_id = ‘SP001‘;	db.book.count({ "publisher.name": "SitePoint"});This presumes denormalized documents is used.
Return the number of book format types
SELECT format, COUNT(1) ASTotalFROM bookGROUP BY format;	db.book.aggregate([ { $group: { _id: "$format", total: { $sum: 1 } } }]);This is known as aggregation: A new set of documents are computed from an original set.
Delete all SitePoint Books
DELETE FROM bookWHERE publisher_id = ‘SP001‘;Alternatively, it ' s possible to delete the record and has this publisher cascade to associated book records if foreign keys is specified appropriately.	db.book.remove({ "publisher.name": "SitePoint"});

SQL VS NoSQL Performance

Perhaps the most controversial comparison is that, in general, NoSQL is faster than the SQL language. There's nothing to be alarmed about. The simpler denormalized storage in NoSQL allows us to get all the information for a particular item in a single query. You do not need to use complex join operations in SQL.

In other words, the design of your project and the need for data will have a big impact. The design of a good SQL database will certainly perform much better than a poorly designed NoSQL database, and vice versa.

SQL VS NoSQL Scale

As the amount of data grows, we may find it necessary to distribute the load to different servers. The development of a system based on SQL language is very difficult. How do I allocate the relevant data? Clustering is one of the simplest possible solutions, with multiple servers accessing the same central storage-in a timely manner there are also many problems.

NoSQL's simple data model simplifies its process, and many NoSQL databases build the ability to solve large-scale data at the outset. This is just a generalization, and if you encounter such a problem you should seek the help of an expert.

SQL VS. NoSQL Feasibility

Finally, let's consider security and systemic issues. The popular NoSQL databases have been around for years, and they may show more problems than mature relational databases. Many problems have been discovered, but all the questions point to the same question: the degree of understanding .

Developers and system administrators have little experience in managing new types of databases, which can lead to many problems. Choosing NoSQL is because it feels new, or if you want to avoid the design of data patterns, it will bring some problems in the future.

SQL VS NoSQL Summary

SQL and NoSQL databases just do the same thing in different ways. We may choose one of them and replace them on the other, but it will save a lot of time and money to make a plan before choosing.

Projects for development with SQL:

• The need for logically related discrete data can be pre-defined
• Data consistency is necessary
• Proven technology with good developer experience and technical support standards

Projects developed for use with NoSQL:

• Irrelevant, uncertain and evolving data needs
• Simpler or looser projects that can start programming quickly
• Speed and scalability are critical

In our case, a relational database is a better choice-especially when we need to introduce powerful transactional support for e-commerce devices. In the next article, we'll discuss more project scenarios and determine whether using a SQL or NoSQL database is the best solution.

A comparison between relational database and NoSQL