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.

How SQL is structured

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:

shoppers place orders on the website,
every order lists several items specifying product title and quantity,
orders to ship out, guided by the shipping info submitted by the customer.

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).

ispmanager interface — Sample simplified diagram where the boxes represent tables of orders, products, and customers. Every attribute has a name and data type: e.g., numeric for the prices of items, and variable character data (varchar) for product titles. The fields known as "keys" serve as links between the tables: order_id, item_id, and customer_id

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:

SQLite lets all data be recorded in the same file. This caps the DBM system's capabilities, but it makes transmission and service much easier, which explains why this database is used in mobile apps and prototyping.
MySQL is a vastly popular database solution, combining the basic SQL capabilities with easy administration. But when the database gets really big, MySQL will falter and have trouble with scalability.
MariaDB is a MySQL evolution, synced with its predecessor's codebase but exhibiting better productivity.
Percona Server, another MySQL offshoot, boasts advanced scalability on multicore servers
PostgreSQL, a prime alternative to MySQL, is distinguished by its thoughtfully conceived architecture, clear documentation, and support of supplementary data breeds, including JSON.

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.

How NoSQL is structured

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:

MongoDB can handle huge data flows and massive amounts of storage, and it is easy to scale. The downside is its complicated transaction implementation process, transactions being a crucial category of encrypted data queries. MongoDB comes with its own language of queries, MQL, but, as with relational DBMS, programmers typically opt for ORM solutions instead.
CouchDB — is designed primarily as a database for web applications. It is ideal for statistical processing where data in the past does not change.

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:

Redis is a key-value database often used for data caching purposes. In one of these applications, Redis would mostly handle RAM data, while a slower relational database would interact with hard disk data.
Cassandra combines the key-value store paradigm with the column family concept. The store has a prompt response rate, is reliable and has a high throughput capacity, especially for data records. These properties of Cassandra are harnessed for Big Data purposes.

SQL or NoSQL? It's all about the use case...

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

If your online service will handle payments, and all transaction parties can be easily profiled in tabular form, go with a relational database.
Where your business project targets objects of a kindred nature with a dissimilar internal structure, such as, for instance, when content management is involved, a document-oriented database would be a better choice.
If your service stresses prompt retrieval of objects from and addition of objects to memory, and long-term storage is not vital, a key-value database will do just fine.

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.