Relational Databases (SQL) vs Non-Relational (NoSQL)
Photo by Taylor Vick on UnsplashA relational database management system (RDBMS) is a type of database management system that is most commonly associated with the SQL query language. On the other hand, NoSQL (standing for not only SQL) is associated with a non-relational type of database management.
Relational databases came before their non-relational counterparts. They require a predefined schema indicating rules that the structure and relationships within the database must follow. Every state of the RDBMS must fulfil the integrity constraints set out in the schema, and so, relational databases are very structured and inflexible in their format.
As a result, they work well for data structures that are simple and static, in other words, where the data tends not to change after being recorded.
In terms of their structure, relational databases represent data in terms of tuples which are grouped into relations. These relations are referred to as tables, and each table has a certain number of rows/records (where each corresponds to a tuple) and columns/attributes. Each column/attribute has a data type (such as integer or string, for example), and every element in a column must adhere to the specified data type.
In a relational database, records, and the relationships between records in different tables, are defined through keys. Primary keys are attributes that uniquely identify rows within a table. These primary keys can then be included as attributes in rows of other tables to indicate a relationship between records across tables. When a primary key from one table denotes a relationship in another table, it is called a foreign key.
As an example, we can consider a table containing all the employees of a sales company, each of whom has a unique employee ID which acts as the primary key in the employee table. We also have a different table containing all the sales made by the company, each of which has a unique sales ID, the primary key in the sales table. But, the company may wish to track which employee made each sale, and so we would also include the employee ID as an attribute in the sales table. In the sales table, the employee ID is called a foreign key since it indicates a relationship between tables but is not a unique identifier in the sales table (since each employee can make multiple sales).
Photo by Claudio Schwarz on UnsplashDue to the predefined schema and relationship structure, several advantageous properties are associated with relational databases. Firstly, referential integrity enforces consistency between two relations. One consequence is that every foreign key must point to an existing primary key. For example, if we try to delete an entry in our employee table above, we must also delete every record in the sales table which relates to that employee, otherwise we will have a broken/inconsistent link. Secondly, every database transaction must adhere to four properties, ACID (atomicity, consistency, isolation, durability), otherwise, the transaction will be rejected. These properties ensure the database remains in a valid state despite errors, server crashes, and other potential problems.
In recent years, however, the application of big data technologies has expanded into a wide variety of domains where data can be dynamic and lack structure. Due to the inflexibility of traditional relational systems, non-relational database management systems have become a necessity to deal with the ever-changing demands placed on database management systems in practice.
In contrast to RDBMS, non-relational (NoSQL) systems do not require a predefined schema. As a result, the schema is dynamic and adaptable, allowing the user to deal with data that cannot be clearly segmented into entity tables with predefined relationships. In addition to allowing for unstructured data, non-relational systems also provide better, cheaper and easier scalability. The strict schema that characterises a relational system ensures its accuracy and consistency, but also comes with the disadvantage of hindering horizontal scalability. It is very hard to have a distributed system that adheres to ACID, and as a result, relational systems lend themselves to vertical scaling (adding more resources to the existing server rather than adding more servers). NoSQL systems, on the other hand, with their less stringent constraints, allow for both transactions and load to be distributed across multiple servers (horizontal scaling).
Photo by Massimo Botturi on UnsplashHowever, as the CAP model tells us, any database system can only provide two of the following three guarantees: consistency, availability and partition tolerance. The added flexibility offered by non-relational systems comes at a price, we swap ACID for BASE (Basic Availability, Soft-state, Eventual consistency). Of the three guarantees in the CAP model, BASE prioritises data availability while ACID prioritises consistency. A BASE system, though eventually consistent, does not enforce immediate consistency, a major difference from an ACID-compliant relational database.
With the strengths and drawbacks of each database management system in mind, it is impossible to make a definitive ruling on one over the other. The choice between a relational and a non-relational (NoSQL) system must be made with the user's unique use case in mind. For example, a social network feed is dynamic, unstructured and contain huge amounts of streaming data; this would be a use case where we need the flexibility of NoSQL.