SQL – it’s like the database whisperer! Back in the day companies stored their records and documents in file cabinets, for large businesses this meant tons and tons of filing cabinets to accommodate all the documents. Now days we have the luxury of storing data in digital databases, although finding information within these digitised databases is not as simple as opening a filing cabinet drawer. For searching databases, Structured Query Language plays a massively important role!
SQL (Structured Query Language) sometimes pronounced ‘sequel’ is a popular domain-specific language designed to access and manage data in a relational database. As a normal person, one cannot simply fire up a database and sift through its contents, as the data is written and stored in a way that we can’t understand, luckily SQL does – therefore we use SQL to communicate with the database! A normal relational database consists of built relationships between defined tables although NoSQL is a means of storing and retrieving data that is presented in a format other than a table. NoSQL is non-relational where all of the information is stored in one place with less relational merging.
Let’s go more into it:
The main difference is SQL being table-based consisting of a primary key, foreign keys and attributes, whereas NoSQL uses various database technologies such as key-value, document, graphs and column databases. In a key-value database, data is stored as attributes (keys) and each attribute has an appropriate value.
The different databases:
A document database consists of many key-value pairs.
A graph database stores data that forms part of a network, consisting of connections.
In a column database, data is stored in columns as opposed to rows.
Vertical Scalability vs Horizontal Scalability
SQL databases are vertically scalable, meaning that capacity is increased by making machines more powerful however there are limitations in the maximum allowable capacity a machine can have and the cost of scaling upwards.
NoSQL databases are horizontally scalable where nodes (i.e. machines) are easily added or removed. This makes NoSQL cloud-friendly allowing for scalability and cost-efficiency. SQL databases can be scaled horizontally but this would require extensive effort and increased costs.
Predefined vs Flexible
SQL consists of a predefined schema where each record entered into the database has values for the fields in the database schema, therefore records cannot have their own unique fields.
NoSQL, on the other hand, has a dynamic schema which allows for flexibility when adding new data as the records do not have to have values for each field. This allows for new data to be stored in the same existing collections and allowing data to be added at any given moment.
Complex Queries vs Hierarchy
SQL makes it possible to easily run complex queries and perform reporting.
NoSQL is fit for hierarchical data stores, where data is stored in a tree-like structure (child and parent).
ACID vs CAP
For SQL the ACID standard is used to ensure Atomicity, Consistency, Isolation and Durability for reliable databases.
Similarly for NoSQL, the CAP theorem is followed to ensure that Consistency, Availability or Partition tolerance is guaranteed in the database.
So the ultimate question remains, which is the ideal for my company? Well, choosing between a SQL and NoSQL database depends on what the database is required for. SQL databases are appropriate for situations where complex queries need to be executed and reporting is required. However, SQL databases should only be used when a well-defined relationship exists between different tables. Since SQL databases are only vertically scalable, it is most appropriately used when no growth or change is anticipated for the database system. Which can sometimes be difficult to foresee or predict.
NoSQL databases are appropriate for situations when speed and scalability are of utmost importance. Since NoSQL databases have a dynamic schema, the database structure can be evolved over time allowing for greater flexibility and long-term efficiency. Since NoSQL is horizontally scalable, costs can be controlled depending on the demand for capacity.
However, there are instances where both NoSQL and SQL databases can be used. It all comes down to NoSQL databases ability to be used to allow for speed in data retrieval and ease in scaling, and SQL databases ability to be used on a portion of the data in the NoSQL database, to conduct reporting and perform complex queries on this data.
While there are many databases to choose from, some popular SQL databases include MySQL, MS Access and PostGreSQL and some popular NoSQL databases include MongoDB, DynamoDB and Cloud Firestore.
SQL and NoSQL can be extremely beneficial to your company, although it is of utmost importance to know which database is the right fit for you. Pop us an email and we’ll help you make an informed decision! web@piidigital.co.za
