SQL-Enabled Vrs. NoSQL Databases

Please follow and like us:

SQL stands for structured query language. It is the language for interacting with relational databases which can also be referred to as SQL-enabled databases. Relational databases are made up of tables with rows and columns. SQL commands work on tables. Examples of SQL commands include SELECT, INSERT, UPDATE, and DELETE.

Perhaps the most common SQL command begins with SELECT.

For example:

SELECT * FROM products

This command selects all of the fields from the products table, however many fields the table has.

Every table has at least one unique identifier known as an index.

sql table example

Relational databases allow the linking of multiple tables via their indexes.

One SQL statements can refer to multiple tables using the SQL keyword JOIN.

Types of relationships among SQL-enabled (relational) database tables include one-to-one, one-to-many, many-to-many, and self-referencing.

SQL has strict rules about the types of data that can go into fields. For example, when users add a field to a table, each record in that table must include that field even if it is empty in that particular record.

Relational databases get around this issue by linking tables. Linking tables can allow either one or many members of the same field as long as each new field instance also has a unique index. For example, one customer can have multiple email addresses.

SQL statements referring to multiple tables can quickly become complicated. If a relational database runs a complex SQL query one time, this added complexity is rarely an issue.

However, if tens of thousands or even millions of complex SQL queries have to run at the same time, performance can slow down dramatically.

SQL-enabled (relational databases) are meant to store data on one server. So scaling involves upgrading the server to hold more data and have more ram and increase CPU speed. This type of scaling gets costly very quickly. Another solution is to have the data in a primary and secondary configuration with the writable data on one server and the read-only data on other servers. This solution runs into the issue of syncing the data.

vertical scaling diagram showing a relational database upgrade
Relational databases are most easily scaled vertically through upgrading to a more powerful server.

Under certain circumstances, high volumes of concurrent database queries may need better performance than just improving a server’s RAM, and CPU can provide. Such upgrades in performance requirements may make a NoSQL database a viable option.

NoSQL databases or not only SQL databases are much more flexible than relational databases in their configurations, their data types, and their schemas.

Types of NoSQL Databases

NoSQL databases include document databases such as the popular MongoDB, as well as key-value stores, and column databases.

In MongoDB for example, “‚Ķdocuments in a single collection do not have to have the same set of fields,” according to the MongoDB Manual. Also, a field’s datatype can differ across a collection’s documents. So if one person inputs text.

MongoDB (a NoSQL database) document example with embedded documents contact and access

MongoDB allows users to modify a collection’s document structure including adding new fields or removing existing fields. It also lets users change the field values to a new type, or update the documents to a new structure. For example one record could have text input in a certain field while another could have integer input in that field. This flexibility differs from SQL-enabled databases in which data types are not easily changeable once they are defined.

Documents in NoSQL databases are somewhat analogous to tables, but they are much less strict about their rules. Sometimes, a single operation can modify multiple documents.

On the other hand, referencing multiple documents can reduce performance. Therefore, the MongoDB Manual encourages the use of a single document schema with embedded documents for enhanced performance.

NoSQL databases, unlike traditional SQL databases, can be readily scaled horizontally. In this type of scaling, the data storage can be divided up and partitioned among multiple servers in a process called sharding.

diagram of a single server scaling to multiple servers through sharding with MongoDB and NoSQL databases
Diagram of a single database server being scaled to multiple servers through sharding.

Reference

MongoDB Inc.(2008). MongoDB Manual. Retrieved from MongoDB Inc. (2019, Sept. 23) . Data Modeling Introduction. https://docs.mongodb.com/manual/core/data-modeling-introduction/#data-modeling-introduction

Raptis, D. Why is it said that relational databases do not scale? (2018, Feb. 22) Retreived from Quora.com (2019, Sept. 24) in reply to question. https://www.quora.com/Why-is-it-said-that-relational-SQL-databases-do-not-scale

Please follow and like us: