SQL or NoSQL: which is better for a database?

This article was prepared by the UltaHost hosting team. Our partners offer rent of virtual and dedicated servers, hosting services with the use of ispmanager

Sometimes it is not clear why all types of databases are needed - relational, document-oriented and key-value databases. It happens that you use one familiar for different tasks and, it seems, everything is fine - shortcomings and results seem to be acceptable.

Maybe one DBMS really is enough. Or maybe life will be easier if you add another one, or change it altogether.

In this article, we will talk about two popular groups of databases with different approaches to data storage — SQL and NoSQL. Let's figure out what tasks they are suitable for.

General idea. Let's say you are trying to set up an online store with a delivery service. The web application uses these linked entities:

These data clusters can be conveniently represented as interlinked tables of Customers, Products, Orders, and Shipments. Databases consisting of such tables are called relational. Each row in the tables corresponds to an entity, and the columns describe the attributes of the entities. The attributes of a product would be its name, type, country of origin, shelf life, weight, and so on. Modern relational databases visualize these tables in editable graphic form. Meanwhile, "under the hood" and whenever automation is at work, you’ll find a standard database query language, the Structured Query Language (SQL).

SQL is a programming language purposefully designed for use with relational databases. It is a declarative language, describing the anticipated result but not the method of obtaining the same. Let's say a marketer wishing to assess the advertising outcomes asks the database administrator to clarify the order statistics by date. Here is a sneak peek over the administrator's shoulder, at the relevant SQL query:

SELECT order_date, COUNT(order_id)
FROM orders
GROUP BY order_date
ORDER BY order_date DESC
LIMIT 20

In this query, we take order data from the orders table and batch it by the date (order_date), tallying the total number of orders for each date, and deducing the 20 latest records in reverse chronological order (DESC). As you can see, we can only describe what we seek to obtain with an SQL query. It's the DBM system's job to find the best way to fulfill our query.

ORM. The SQL's declarative style is not what beginner-level programmers are accustomed to. But the good news is that most of the major programming languages come with an embedded Object-Relational Mapping (ORM) concept. Its specialty libraries supplant SQL operations with structures inherent to the programming language used. In Python, SQLAlchemy impersonates ORM, and Sequelize stands in for Node.js. Ultimately, the entire logic of data query processing can be stored inside the program's source code.

Strictly speaking, SQL is only a language standard. Every individual DBMS will deploy it in its own way, adding extra features to the language as necessary for its particular use case. Just like a natural language, these variations of the standard are called SQL dialects: each DBMS speaks its own. Here are a few examples of widespread relational databases:

It is easy to test all these databases on the ispmanager data panel and select the most suitable solution. The panel will also let you manage the databases without having to make forays into the administration panes: you can create and delete databases, edit user rights, and install supplementary DBMS editions.

General idea. Not all data will lend itself to representation in a rigid, interrelated tabular structure. Tables are useless when it comes to content storage, data caching, and representation of loosely coupled structures. Tables also require a new column for each new attribute. However, when some of the properties are not mandatory, many cells will end up empty, which is counterproductive in terms of data storage.

These two considerations alone justify an alternative to the relational approach: NoSQL. NoSQL is not a single concept but an umbrella term for a diversity of concepts. Here we will talk about two of the more popular NoSQL categories: the document-oriented database and the key-value database.

A document-oriented database assumes that objects of varying structure may correspond to the same single entity. Imagine you are a developer working on a web game where the character collects and uses various objects. Every object endows the character with different properties: some enhance the character's agility, others reinvigorate him while he sleeps, and others still increase his endurance. These are all inventory objects, but it makes the most sense to only store specific attributes for every object.

In this and other similar development tasks, data structure gets described in document form, a JSON-like structure with keys and values. Objects of the same entity may possess different sets of attributes. Here are a couple of popular document-oriented DBM systems:

Key-value databases. There are more basic sets of needs where a complex object structure may be dispensed with, and all that matters is the ability to retrieve the target object from memory faster. Key-value databases handle this job marvelously. Let's take an online weather service. The website stores the entire prior history of weather values, but the information most frequently requested by users is the weather for today and the forecast for tomorrow. It makes sense to store and update this data directly in server memory, matching air temperature in degrees Celsius (value) to the geographic location (key) on the current weather page.

Here are a couple of good examples of such databases:

Choosing a database is a matter of one's business needs.

Do not try to cover all tasks with the same database, it's par for the course to use multiple DBMS types, with each assigned its own set of responsibilities.

In our Facebook community, let us know which databases you wish to learn more about, and we'll respond with the appropriate material.