The difference between SQL and NoSQL (relational and non-relational) databases

Always the right classic answer is still: specific problem specific analysis.

Data tables vs. datasets

The main difference between relational and non-relational databases is the way data is stored. Relational data is naturally tabular, and therefore stored in rows and columns of data tables. Data tables can be associated with each other for collaborative storage, and it is easy to extract data. In contrast, non-relational data is not intended to be stored in rows and columns of a data table, but rather as chunks grouped together. Non-relational data is typically stored in a dataset, like a document, a key-value pair, or a graph structure. Your data and its characteristics are the primary determinants of how you choose to store and extract your data.

Pre-defined structures vs. dynamic structures

Relational data usually corresponds to structured data because the data table has a predefined structure (the definition of a column), and the structure describes the form and content of the data. This is critical to modeling data, and you must "define the structure first". Although the pre-defined structure brings reliability and stability, the table structure that has been deposited into the data is very painful to modify. On the other hand, non-relational data is based on a dynamic structure, which is usually suitable for unstructured data. Non-relational data can easily accommodate changes in data types and structures, because the dynamic structure itself supports this.

Storage Normalization vs Storage cost

The data storage of the relational database is for the sake of higher standardization, separating the data into the smallest logical table (relational table) to avoid duplication and get the most streamlined space utilization. While data normalization makes data management clearer, it often brings a little bit of complexity, especially when a single operation can involve multiple relational tables, and data management is a bit cumbersome. In addition, more streamlined space utilization can often save valuable data storage, but in today's world we can basically assume that the cost of storage (disk space) is negligible. While non-relational data is stored in a flat dataset, data can often be duplicated. A single database is rarely separated, but stored as a whole, so that it is easier to read and write for the whole piece of data.

Vertical Expansion vs Horizontal expansion

The biggest difference between SQL and NoSQL databases may be in scaling, and of course scaling up to support the growing demand. To support more concurrency, SQL database is scaled up, that is, it improves processing power and uses a faster computer, so that processing the same data sets is faster. Because the data is stored in a relational table, the performance bottleneck of the operation may involve many tables, which need to be served by improving the computer's performance. Although the SQL database has a lot of room to expand, it will eventually reach the upper limit of vertical expansion. NoSQL databases are scale-out. Non-relational data storage is naturally distributed, and NoSQL database extensions can share load by adding more common database servers (nodes) to the resource pool.

Structured queries vs unstructured queries

relational databases manipulate data through the so-called Structured Query language, which is what we often call SQL. SQL Support Database crud (add, query, update, DELETE) operations are very powerful and are industry standard usage. A non-relational database manipulates data as a block (like a document) and uses the so-called unstructured Query Language (UNQL), which is not standard and differs from one database provider to another. The concept of a primary key in a relational table corresponds to the document ID in the non-relational store. SQL databases use predefined optimizations, such as column index definitions, to help speed up query operations, while NoSQL databases employ simpler and more accurate data access patterns.

Mapping vs Localization

The choice of SQL and NoSQL data stores also depends on the developer, although this factor has little impact. Developers with object-oriented programming languages typically work with one or more data entities (including nested data, lists, and arrays of complex structures) to pass data to the application user interface. If the underlying database is discussed, things are not always fair. In relational storage, data entities typically need to be normalized in multiple parts and then stored separately into multiple relational tables for thin storage. Fortunately, this is a long-standing problem, and most programming platforms have a corresponding simple solution, such as an ORM layer (object Relational mapping). ORM is a mapping layer between relational data sources and object-oriented data entities used by developers. However, for non-relational storage, there is no need to normalize the data, and complex data entities can be stored as a whole in a separate unit. Objects used in an application are typically serialized as JSON strings and stored in a JSON document in a NoSQL database.

Transactional vs Pure extensibility

If your data operations require high transactional or complex data queries to control the execution plan, then traditional SQL databases are your best choice for performance and stability considerations. SQL database supports fine-grained control of transactional atomicity and easy rollback of transactions. Although NoSQL databases can also use transactional operations, their true shining value is in terms of operational extensibility and large data volume processing.

ACID VS CAP

The prestigious value of SQL database is to ensure data integrity through so-called acid attributes (atomicity, consistency, isolation, durability), and most relational storage vendors support acid. Our goal is to support the isolation of indivisible transactions, whose changes are persistent and data is in a consistent state. The NoSQL database lets you choose between any two of the Caps (consistency, usability, partition tolerance), because in a node-based distributed system it is difficult to do all three.

Data vs Big Data

SQL database can reliably store and process data, and NoSQL's biggest advantage is in dealing with big data, which is a large number of unstructured data entities generated by our society or computer on a daily basis. NoSQL uses a modeless approach to data management, so its scale-out potential is limitless, which may be the only effective way to deeply handle big data capture, management, retrieval, analysis, and visualization.

Data logging vs Internet of things and people networking

relational databases streamline storage on the basis of data normalization and performance assurance. But in recent years, we have produced data much faster than relational storage can meet the growth of storage capacity. The reason for this rapid increase in data is the huge number of users and the Internet of things. Users connected to the Internet are multiplying and using our apps in sync. As large numbers of mobile device data-sensing devices are connected to the Internet, the amount of data generated by the machines increases dramatically. Enterprises must therefore seek NOSQL technology and infrastructure to handle the continuous influx of semi-structured and unstructured data.

On-Premises vs cloud computing

Cloud computing is now ubiquitous, and it combines the benefits of SQL and NoSQL databases. Relational storage in a cloud environment is typically delivered as a service, replicable, highly available, and distributed, greatly improving scale-out. NoSQL databases hosted in cloud services also naturally enjoy the benefits of automatic sharding, which can be flexibly processed on a phased basis, with integrated caching and huge computing power to capture, store, and analyze big data.

Pay vs Open source

One view is that SQL databases are mostly expensive, and NoSQL databases are usually open source. In fact, both types of databases are open source and commercial. The common SQL database is Microsoft's SQL Server,mysql,sqlite,oracle and Postgres. The popular NoSQL databases are couchbase,mongodb,redis,bigtable and RAVENDB.

Both SQL and NoSQL have their pros and cons, and choosing the right architecture depends on the need to build your application.

Source: http://m.blog.csdn.net/article/details?id=51779361

The difference between SQL and NoSQL (relational and non-relational) databases