What is the difference between SQL and NOSQL ?
//////////////////////////////////////////////////
SQL
SQL - which is named for the language it's written in: Structured Query Language
Developed by IBM in the 1970s, and is the traditional relational databases where you have rows and columns or what you might define as records and fields inside a table, and very often SQL database is built with a set of tables which compose that particular database : for a certain department in a company, hospital or school for instance.
SQL also allows you to perform CRUD operations [ Create, Read, Update and Delete]
SQL is the language which is consistent across different relational database management systems like Oracle, MySQL, PostgreSQL etc.
so it's now safe to say that SQL is the language and MySQL is the DBMS; and it's not the only DBMS as mentioned before we have many such as Oracle, MS SQL Server, Sqlite3 etc.The overall structure of any SQL database is mainly in Tables , and each table consist of rows and columns
Rows represent the records in that table, and columns represent the field of each column, for
example :
So here we have an employee ID, last name, salary and region id.
Every row here is a record in the table, so E32561 is the unique employee number of Williams with a salary of 4,500 and have a region code 0001.
And in a table you must have a Primary key which is a unique identifier that identifies this particular row, and you might have a foreign key, and a foreign key in one table is a primary key in another table, and that's where the word relational database comes from, the relation between tables is established through these connections between primary and foreign keys.
so here for instance, you have Williams with the region code 0001, and we don't understand what the code signifies, but if we will look to the regions table, we will find that region code 0001 corresponds to Northeast, 0002 to Midwest and 0003 to South.
And this allows us to run Queries very easily, for instance you might want to query all employees in Midwest, so what you will do is to say :
SELECT * FROM EMPLOYEE WHERE REGION_CODE = 0002;
so this really helps to perform such flexible queries.
And you have also attributes or data types for each column, for instance last name is a data type of VARCHAR or variable character; salary is an integer for instance, you might have also joining date is of data type DATE.
SQL databases also enforce constraints, and constraints are used to define the rules for the data in that table; in other words they're used to limit the datatype that can go into a table. and the reason for that is to ensure the accuracy of data in a table - Primary Key and Foreign key are examples of constraints, you have also other constraints such as NOT NULL [ to ensure thata column cannot have a null value], you have UNIQUE [which ensures that all values in a column are different ] and many more constraints can be added too..
Usually, databases are stored in one machine which has the entire database.
By the way, scaling the database can be horizontal or vertical
In SQL relational datase systems, the scaling of the database is vertical which means :
Vertical scaling means having a better computer which means better central processing unit (CPU), better memory, bigger hard drive and so on - this technic is used when you are facing difficulties with your current machine in terms of performance.
While
Horizontal scaling means adding more computers or machines in a horizontal fashion, and this way you will distribute your data across multiple computers horizontally.
So you have one main computer and multiple read replicas from which data can be extracted, and this helps in offloading the pressure from the Master computer - and the result will be an optimal performance on the database.
And this is the case in all NOSQL database systems
You also will require a direct connection to the database endpoint;
And nowadays we have a lot of ORMs or object relational mappers , and ORMS create virtual object database that can be functional within any programming language, such as SQLIT3 in Django framework for instance, POSTGRESQL or SQLALCHEMY connections for Python is another example of ORMs.
so they provide a link between database tables including records and fields and between Python objects, for example.
We have covered almost everything that you need to know about SQL here, let's move on to NOSQL
//////////////////////////////////////////////////
NOSQL
NOSQL is very different from the traditional relational database; there are no tables with columns and rows
It is based on what we call the document-model or Documented-Oriented database
And the main concept of a document store is that of a "document".
While the details of this definition differ among document-oriented databases, they all assume that documents encode data in some standard formats or encodings like : XML, YAML, and JSON and binary forms like BSON.
There are many forms or implementations for NOSQL systems, so some takes the form of Documents written in BSON with key-value pairs, also in form of Graphs, and Column family databases.
NOSQL was originally built to solve the high performance issue, and it succeded indeed to scale with high performance without overclocking the servers, but everything has a cost and the cost was that your queries are less flexible.
STRUCTURE :
1 NOSQL can be implemented under the form of Documents, which in fact looks like a Javascript object or more like a JSON document, but actually it's not JSON but BSON, Binary Script Object Notation and the difference is that BSON has more additional data types;
2 There are Column family database that contains columns of related data, and it consists of a key-value pair; it's a analogist to a table in a relational database system.
3 And also there are Graphs implementations of NOSQL; and a clear example for that is Linkedin or Facebook where you have friends and your friends have friends and so on; so Graph database is very suitable for such representation.
These three forms of NOSQL representation rely on a KEY:VALUE stores; so in a NOSQL database you have to know the key you're looking for, when you are performing your query.
To access NOSQL database systems, you can rely on REST API to contact a certain NOSQL endpoint that has a certain functionality.
And I will have a Crash Course for MongoDB where we will take a closer look what is going on with a document object implementation of a NOSQL database system.
So now the question,
What to choose and when ?
In choosing between SQL vs. NoSQL, you’ll need to think about what your data looks like, how you’ll query it, and your scalability needs.
Share your thoughts about both SQL and NOSQL, and let's all learn from each other's experiences.
Thank You for reading.
Top comments (0)