Database Management Systems
What is Data?
In order to fully understand the workings of a database, let’s first discuss data. Put simply, data can be facts related to any object in consideration. Examples of data include text, numbers, images, videos, etc. If it can be recorded, it can be considered data.
A database is a systematic collection of related data. Usually databases have been applied for data that is either text or numbers. These are considered traditional databases. The term ‘data warehouse’ is used for very large databases that include long term historical data.
Why databases are needed
Databases allow managing extremely large amounts of data very easy. They also ensure accuracy within the data. Spreadsheets typically involve manual data entry which could become very messy. Uploading data to a database, be it unstructured, semi-structured, or fully-structured is much easier. And most importantly, databases also ensure better levels of security. They allow data to get encrypted so only authorized users have access to it.
SQL
SQL is simply the standard language for dealing with Relational Databases which can be used to create, read, update, and destroy database records. It stands for Structured Query Language. A Relational Database is a type of database that stores and provides access to data points that are related to one another. All the data is stored in the form of tables.
How do we differentiate the data in these tables? Each row in the table has a key. This gives a unique identifier to each entry in a particular table. The columns hold all the attributes of the data. Each record has a value which is associated with the particular attribute. This makes it very easy to establish relationships among the data. Some Relational Database examples include Postgres, MySQL, Oracle, and SQLServer.
NoSQL
NoSQL is a non-relational Database Management System (DMS), that does not require the fixed relationships or joins that are present in SQL. This makes NoSQL very easy to scale from one machine to thousands of machines where all the data needs to be processed at the same time. The biggest advantage is their ability to handle large data storage requirements.
NoSQL consists of a wide variety of database technologies. This is done to ensure you can store any type of data in a very simple way. Facebook, Google, Twitter and many other IT companies go about using noSQL for their day to day activities. Some None-Relational Database examples include Amazon DynamoDB and MongoDB.
SQL vs. NoSQL
Definition: Just to review, SQL databases are called Relational Database Management Systems (RDBMS). NoSQL databases are called Non-Relational or distributed databases.
Development History: SQL databases were developed in the 1970s, making the technology over 50 years old. It came into picture when there were some struggles to flat file storage methods. NoSQL was developed in the late 2000s, making it a relatively new technology. It was established to overcome all the limitations and downsides of the SQL databases.
Database Type: SQL databases will alway be table based. Tables form the backbone of this entity and every item serves a vital role in that database. NoSQL databases include everything from graphs, documents, key-value paired databases and much more.
Scalability: Scalability is another place where the two database types can really differ. SQL databases are all vertically scalable and can be stacked one on top of the other. Hence why the logo looks like a “stack” of data. NoSQL databases are know to be horizontally scalable, next to each other at the same time.
Ideal Usage: Ideal use case scenarios for SQL databases include having very complex queries and other query intensive work. NoSQL is a little bit backward when it comes to complex queries. It is usually considered when the queries are not complex.
Ability to be open source: Not all SQL databases are open source, but 90% of them are. There are commercial versions where some of the features may come at a premium. Most NoSQL databases have been open source ever since their launches.
Hardware requirements: SQL sometimes requires very specialized hardware. NoSQL databases work with full functionality on full screen, even on particular commonplace hardware and does not have specific requirements.
When to use each one
SQL is used when you have to analyze any behavioral related tasks or give any customized reporting to your clients. It is also preferred when you are building custom dashboards. SQL is also the go-to language when making use of RDBMS concepts such as joins or views.
NoSQL is used where we have a non-relational database model. It is used when there are requirements from a flexible schema. It does not have any particular dependancies and can be used when constraints or validations are not required. NoSQL is also more efficient when it comes to logging data from a variety of sources.
Relational Database Comparison: MySQL vs PostgreSQL
There is not much difference between MySQL and PostgreSQL when it comes to core functionality. Both are well established and open source databases. They can be run either locally or in the cloud.
While MySQL is still the more popular RDBMS, PostgreSQL is quickly gaining ground.