SQL & NoSQL … porque no los dos?
We were taught about structured query language (SQL) first which is a special-purpose programming language that is used for managing relational database management system (RDBMS). It involves multiple tables that are connected through special keys called primary keys and foreign keys. I was having trouble understanding some of the concepts in the beginning, but after drawing up a unified modeling language (UML) diagram, it was easier to see how each table is connected by a specific key. A table can have one to one , one to many, or many to many relationship. A one to one is where one element of table A is linked to one element of table b. For example, only one country can be linked to one city. Whereas a one to many relationship connects one element from table A to many elements in table b, and where table b is only linked to one element in table A. You can see from the example below, that a user represented by the “ | ” symbol can only be one user, but may have multiple posts represented by the “crows feet”. Finally a many to many relationship can be seen as an Author having written many books and books have been published by many authors.
After getting familiar with SQL, we dove into not only SQL (NoSQL), which is another type of database which does not use RDBMS. This means that its a type of database that manages it data in a non-relational type of way, so instead they can be stored as key-values, graph, or document stores. We are currently using Redis as a key-value datastore for our assignments in class.
So why are there 2 different types of databases SQL and NoSQL? What are the pros and cons?
NoSQL is faster when dealing with reading and writing from the databases. The reason is because all the data is in one table, also called a denormalized table. An example would be a blog post and comments. Since all that data is in one table, its fast to retrieve the data and load the page. Whereas in SQL, the post table and comment table would need to join first in order to retrieve the data.
- ACID Compliant
ACID is an acronym for Atomicity, Consistency, Isolation, and Durability. Which basically means that the transactions or changes from your queries to the database are not saved or persisted if a query fails for any reason. More RDBM’s in SQL databases are ACID compliant where NoSQL is less compliant, in favor of higher performance.
There are datatype constraints built into SQL RDBM’s which will throw errors if they certain conditions aren’t met when you input data. This preserves the integrity of your data. In noSQL, this strict data-typing has been removed to process data quicker.
If you need to store more data in SQL databases, you would typically use vertical scaling strategies. This requires you to pay more and more money the bigger your server gets. For example, you first server costs 10$, when you decide to upgrade, the price of the new server is $50, and whenever you decide to make a third upgrade it could be $200. On top of the high price, it would take you hours to load all the data from the older server to the new.
In NoSQL databases, its designed to scale horizontally. So if your first server purchase was $30, and then you decide to upgrade, it will still cost an $30 for every time you would like to upgrade.