One of the most important decisions a developer makes is which database to use. For many years, options were limited to the various relational database options that supported the Structured Query Language (SQL). These include MS SQL Server, Oracle, MySQL, PostgreSQL, DB2 and many more.
Over the past 15 years, many new databases have entered the market under the No-SQL approach. These include key-value stores like Redis and Amazon DynamoDB, broad column databases like Cassandra and HBase, document stores like MongoDB and Couchbase, and graph databases and search engines like Elasticsearch and Solr.
In this article, we will try to understand SQL and NoSQL without getting into their functionality.
Plus, we'll have some fun along the way.
Explaining SQL to Granny
Grandma, imagine that I'm not your only grandson. Instead, mom and dad loved each other like rabbits, they had 100 children, then they adopted 50 more.
So, you love all of us and don't want to forget any of our names, birthdays, favorite ice cream flavors, clothing sizes, hobbies, spouse names, offspring names and other super important facts. However, let's face it. You're 85 years old and good old memory just can't handle it.
Luckily, being the smartest of your grandchildren, I can help. So I come to your house, I take out some sheets of paper and I ask you to bake some cookies before we start.
On one sheet of paper, we make a list called "Grandchildren". Every grandson recorded with some significant information about him, including a unique number that will now indicate how grandson he is. Also, for the sake of organization, we write out named attributes at the top of the list so that we always know what information the list contains.
id
name
birthday
last visit
clothing size
favorite ice cream
approved
1
Jimmy
09-22-1992
09-01-2019
L
mint chocolate
false
2
Jessica
07-21-1992
02-22-2018
M
rocky road
true
…we continue the list!
List of grandchildren
After a while, you understand everything and we are almost done with the list! However, you turn to me and say: “We forgot to add space for spouses, hobbies, grandchildren!” But no, we haven't forgotten! This follows further and requires a new sheet of paper.
So I pull out another sheet of paper and on it we call the list Spouses. We again add the attributes that are important to us to the top of the list and start adding in rows.
id
grandchild_id
name
birthday
1
2
John
06-01-1988
2
9
Fernanda
03-05-1985
…more spouses!
List of spouses
At this stage, I explain to my grandmother that if she wants to know who is married to whom, then she only needs to match id In the list grandchildren с grandchild_id in the list of spouses.
After a couple dozen cookies, I need to take a nap. "Can you continue, grandma?" I'm leaving to take a nap.
I'm returning in a few hours. You're cool, grandma! Everything looks great except for the list hobby. There are about 1000 hobbies on the list. Most of them are repetitive; What's happened?
grandchild_id
hobby
1
biking
4
biking
3
biking
7
running
11
biking
…we continue!
Sorry, I completely forgot to say! Using one list, you can only track hobby. Then in another list we need to trace grandchildrenwho are doing this hobby. We're going to call it "Common list". Seeing that you don't like it, I get worried and go back to list mode.
id
hobby
1
biking
2
running
3
swimming
…more hobbies!
List of hobbies
Once we have our hobby list, we create our second list and call it "Grandchildren's hobbies».
grandchild_id
hobby_id
4
1
3
1
7
2
…more!
General list of grandchildren hobbies
After all this work, Grandma now has a cool memory system to keep track of her entire amazingly large family. And then - to keep me longer - she asks the magic question: "Where did you learn to do all this?"
Relational databases
A relational database is a set of formally described tables (in our example, these are sheets) from which you can access According to or collect them in various ways without having to reorganize the tables Database. There are many different types of relational databases, but unfortunately a list on a piece of paper is not one of them.
The hallmark of the most popular relational databases is the SQL (Structured Query Language) query language. Thanks to him, if Grandma transfers her memory system to a computer, she can quickly get answers to questions such as: “Who did not visit me last year, is married and has no hobbies?”
One of the most popular SQL database management systems is the open source MySQL. It is implemented primarily as a relational database management system (RDBMS) for web-based software applications.
Some key features of MySQL:
- It is fairly well known, widely used and extensively tested.
- There are many skilled developers who have experience with SQL and relational databases.
- The data is stored in various tables, which makes it easy to establish relationships using primary and foreign keys (identifiers).
- It is easy to use and efficient, making it ideal for businesses large and small.
- The source code is under the terms of the GNU General Public License.
Now forget ALL.
Explaining NoSQL to grandma
Grandma, we have a huge family. She has 150 grandchildren! Many of them are married, have children, are fond of something and so on. At your age, it's impossible to remember everything about all of us. What you need is a memory system!
Fortunately, I not wanting you to forget my birthday and favorite flavor of ice cream, I can help. So I run to the nearest store, take a notebook and return to your house.
The first step I take is to write "Grandchildren" in big bold letters on the cover of my notebook. Then I flip to the first page and start writing everything you need to remember about me. A few minutes later, the page looks something like this.
{
"_id":"dkdigiye82gd87gd99dg87gd",
"name":"Cody",
"birthday":"09-12-2006",
"last_visit":"09-02-2019",
"clothing_size":"XL",
"favorite_ice_cream":"Fudge caramel",
"adopted":false,
"hobbies":[
"video games",
"computers",
"cooking"
],
"spouse":null,
"kids":[
],
"favorite_picture":"file://scrapbook-103/christmas-2010.jpg",
"misc_notes":"Prefers ice-cream cake on birthday instead of chocolate cake!"
}
Я: “It seems everything is ready!”
Grandmother: “Wait, what about the rest of the grandchildren?”
Я: "Yes exactly. Then allocate one page for each.”
Grandmother: “Will I need to write down all the same information for everyone, as I did for you?”
Я: “No, only if you want. Let me show."
Grabbing my grandmother's pen, I flip the page and quickly jot down information about my least favorite cousin.
{
"_id":"dh97dhs9b39397ss001",
"name":"Tanner",
"birthday":"09-12-2008",
"clothing_size":"S",
"friend_count":0,
"favorite_picture":null,
"remember":"Born on same day as Cody but not as important"
}Whenever a grandmother needs to remember something about one of her grandchildren, she only needs to navigate to the right page in her grandchildren's notebook. All information about them will be stored right there on their page, which she can quickly change and update.
When everything is done, she asks the magic question: "Where did you learn to do all this?"
NoSQL databases
There are many NoSQL databases (“not just SQL”). In our examples, we have shown document database. NoSQL databases model data in ways that exclude the table relationships used in relational databases. These databases became popular in the early 2000s with companies that needed cloud-based database clustering due to their explicit scaling requirements (like Facebook). In such applications, data consistency was much less important than performance and scalability.
In the beginning, NoSQL databases were often used for niche data management tasks. Basically, when it came to web and cloud applications, NoSQL databases processed and distributed significant amounts of data. NoSQL engineers also liked the flexible data schema (or lack of it) so that rapid changes were possible in applications that were updated.
Key features of NoSQL:
- Very flexible way to store data
- Horizontal scaling to clusters
- Possible sequencing per persistence/spread
- Documents that are identified using unique keys
Detailed comparison
MySQL requires a defined and structured schema.
NoSQL allows you to store any data in a "document".
MySQL has a huge community.
NoSQL has a small and rapidly growing community.
NoSQL is easy to scale.
MySQL needs more manageability.
MySQL uses SQL, which is used in many types of databases.
NoSQL is a database based design with popular implementations.
MySQL uses a standard query language (SQL).
NoSQL does not use a standard query language.
MySQL has many great reporting tools.
NoSQL has several reporting tools that are difficult to standardize.
MySQL can show performance issues for big data.
NoSQL provides excellent performance on big data.
Thoughts 8base
The company where I work, we provide a workspace for each project with an Aurora MySQL relational database hosted on AWS. While NoSQL is a logical choice when your application demands high performance and scalability, we believe that the strong data consistency provided by a DBMS is essential when building SaaS applications and other business software.
For startups and developers building business applications that require reporting, transactional integrity, and well-defined data models, investing in relational databases is, in our opinion, the right choice.
Learn more about developing with Aurora, Serverless and GraphQL at 8base.com .
Source: habr.com