In our last post we shed some light on how SQL is a language used to communicate with relational databases. Relational databases, we said, are storage servers that carry collections of linked tables like:
EMPLOYEE TABLE -------------------------------------- | id | name | email | -------------------------------------- | 1 | Joe D. | info@domain.com | -------------------------------------- ADDRESS TABLE --------------------------------------------------------------------------------------------------- | id | employee_id | address | moved_in | moved_out | --------------------------------------------------------------------------------------------------- | 1 | 1 | 908 Law Dr, Mississauga ON, Canada | 2011-09-09 00:00:00 | null | ---------------------------------------------------------------------------------------------------
To carry an archive of addresses for an employee, we used a linked address table with a column to identify which employee id the record belongs to.
In NoSQL similar data would reside in a single JSON block, like the following for example:
{
"employees":{
"1": {
"name":"Joe D.",
"email":"info@domain.com",
"addresses": [
{
"address":"908 Law Dr, Mississauga ON, Canada",
"moved_in":"2011-09-09 00:00:00",
"moved_out":"null",
}
]
}
}
}
Aside from my own commentary on the computational impact of all of this and how it can result in varying degrees of pros and cons of data operations’ efficiency; there is what other experts have to say on the matter.
From my research last year, I didn’t find too many experts giving definitive recommendations on how NoSQL is better or worst than SQL. It seems the NoSQL database is still being evaluated and tested in the real world so definitive proclamations on the subject are difficult to attain.
However here are a few pointers I did find online last year:
- SQL supports transactions. This is important for data integrity in applications like those of a banking system; where certain data operations need to all happen at once.
- NoSQL is likely faster at data retrieval; unless you properly index a relational database in which case you could possibly get serious competition(?).
- NoSQL is more of a data store than a database? One software engineer I was talking to suggested this. This is not my own assertion. His reasoning was that data operations within the database itself can be difficult in a NoSQL DB. So data can be stored in a NoSQL database, but you can’t easily run analytical operations in the database like you could in a SQL DB. Analytical operations have to be done in the application layer.I have yet to validate this theory. However you are free to look into this yourself.
- NoSQL databases focus less on linking data; they have less checks for correct data structure; and you generally can add any valid JSON data into it; so the data operations tend to be less taxing on the physical servers and thus the bandwidth of NoSQL servers is typically (much?) higher than SQL servers. This means higher scalability.
This isn’t much in my opinion.
I will add this however: NoSQL is a new kid on the block. We need more real world examples and more experience to see its strengths and weaknesses. However, we do know of relational databases’ strengths and weaknesses quite well.
Remember that SQL thrives with indexes. Know that large column types like binary and non-binary text fields are not easy to index. Know that indexing is heavy in hard-drive usage; remember that large columns like text and blobs typically have longer retrieval times in queries. If you understand what is under the hood in a SQL database; you can work with what you have; especially for existing applications.
Who said all data for your application needs to reside in one database? Ever thought about migrating out tables that have taxing data operations into a separate database server?
There are ways to overcome SQL’s weaknesses. Talk to us about solutions to overcome scalability issues with your database.
By: Mustafa Ghayyur
July 25, 2018
