One of the most important decisions a developer makes is which database to use. For many years, the options were limited to various types of relational databases that supported Structured Query Language (SQL). These include MS SQL Server, Oracle, MySQL, PostgreSQL, DB2, and many others.
Over the last 15 years, many new databases have emerged in the NoSQL approach. These include key-value stores like Redis and Amazon DynamoDB, wide-column databases such as Cassandra and HBase, document stores like MongoDB and Couchbase, as well as graph databases and search engines like Elasticsearch and Solr.
In this article, we will try to understand SQL and NoSQL without delving into their functionalities.
Moreover, we will have a little fun along the way.
Explaining SQL to grandma
Grandma, imagine that I’m not your only grandchild. Instead, mom and dad loved each other so much that they had 100 children, then adopted another 50.
So, you love all of us and don’t want to forget any of our names, birthdays, favorite ice cream flavors, clothing sizes, hobbies, spouses' names, children's names, and other super important facts. But let’s face it. You’re 85 years old, and your good old memory just can’t handle it all.
Fortunately, I, being the smartest of your grandchildren, can help. So I come over to your house, pull out some sheets of paper, and ask you to bake some cookies before we start.
On one sheet of paper, we create a list titled “Grandchildren.” Each grandchild is listed with some key information about them, including a unique number that will now signify which grandchild they are. Furthermore, for the sake of organization, we write down named attributes at the top of the list so we always know what information this list contains.
id
name
birthday
last visit
clothing size
favorite ice-cream
adopted
1
Jimmy
09-22-1992
09-01-2019
L
Mint chocolate
false
2
Jessica
07-21-1992
02-22-2018
M
Rocky road
true
…let’s continue the list!
Grandchildren list
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 didn’t forget! That comes next and requires a new sheet of paper.
So I pull out another sheet of paper, and on it we call the list Spouses. We add the attributes that are important to us again at the beginning of the list and start adding to the 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 point, I explain to Grandma that if she wants to know who is married to whom, she only needs to match id in the list grandchildren with grandchild_id in the list of spouses.
After a couple dozen cookies, I need to take a nap. "Can you continue, Grandma?" I go off to nap.
I return a few hours later. And you're awesome, Grandma! Everything looks great, except for the list hobbies. The list has about 1000 hobbies. Most of them are repeated; what happened?
grandchild_id
hobby
1
biking
4
biking
3
biking
7
running
11
biking
...let's continue!
Sorry, I completely forgot to mention! Using one list, you can only track hobbies. Then we need to track in another list grandchildren, those who are involved in this hobbies. We're going to call this "Master List". Seeing that you don't like this, I start to worry and switch back to list mode.
id
hobby
1
biking
2
running
3
swimming
...more hobbies!
List of hobbies
Once we have our list of hobbies, we create our second list and call it "Grandchildren's Interests».
grandchild_id
hobby_id
4
1
3
1
7
2
...more!
Master List Grandchildren's Hobbies
After all this work, Grandma now has a cool memorization system to track her amazingly large family. And then — to keep me around longer — she asks the magic question: "Where did you learn to do all this?"
Relational databases
A relational database is a set of formally defined tables (in our example, these are sheets) from which data can be accessed data or assembled in various ways without needing to reorganize the tables databases. There are many different types of relational databases, but unfortunately, the list on a sheet of paper is not one of them.
The hallmark of the most popular relational databases is the SQL query language (Structured Query Language). Thanks to it, if Grandma transfers her memorization system to a computer, she will be able to quickly get answers to questions like: "Who didn't visit me last year, is married, and has no hobbies?"
One of the most popular SQL database management systems is MySQL, which is open-source. It is primarily implemented as a relational database management system (RDBMS) for software applications based on web technologies.
Some key features of MySQL:
- It is quite well-known, widely used, and thoroughly tested.
- There are many qualified developers with experience in SQL and relational databases.
- Data is stored in various tables, which makes it easy to establish relationships using primary and foreign keys (identifiers).
- It is user-friendly and efficient, making it ideal for both large and small businesses.
- The source code is available under the GNU General Public License.
Now forget ALL.
Explaining NoSQL to Grandma
Grandma, we have a huge family. There are 150 grandchildren! Many of them are married, have children, are into various activities, 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, do not wanting you to forget my birthday and favorite ice cream flavor, can help. So I run to the nearest store, grab a notebook, and head back to your place.
The first step I take is to write "Grandchildren" in big bold letters on the cover of the notebook. Then I turn to the first page and start writing everything you need to remember about me. In a few minutes, 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!"
} I: "Looks like everything is ready!"
Grandma: "Wait, what about the other grandchildren?"
I: "Yes, exactly. Then we allocate a page for each one."
Grandma: "Do I need to write all the same information for everyone, like I did for you?"
I: "No, only if you want to. Let me show you."
Taking a pen from Grandma, I flip to the next page and quickly write 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 Grandma needs to recall something about one of her grandchildren, she just needs to go to the right page in the grandchildren's notebook. All the information about them will be stored right there on their page, which she can quickly modify and update.
Once everything is done, she asks the magical question: "Where did you learn to do all this?"
NoSQL Databases
There are many NoSQL databases (“not only SQL”). In our examples, we showed document database. NoSQL databases model data in ways that exclude the table relationships used in relational databases. These databases gained popularity in the early 2000s among companies that required cloud clustering of databases due to their explicit scalability requirements (for example, Facebook). In such applications, data consistency was much less important than performance and scalability.
Initially, NoSQL databases were often used for niche data management tasks. Mainly, when it came to web and cloud applications, NoSQL databases handled and distributed significant volumes of data. Engineers working with NoSQL also appreciated the flexible data schema (or complete lack of it), allowing for rapid changes in updated applications.
Key features of NoSQL:
- A very flexible way to store data
- Horizontal scaling to clusters
- Possible consistency on permanence / distribution
- Documents that are identified using unique keys
Detailed comparison
MySQL requires a defined and structured schema.
NoSQL allows storing any data in a "document."
MySQL has a large community.
NoSQL has a small and rapidly growing community.
NoSQL is distinguished by its ease of scaling.
MySQL requires more manageability.
MySQL uses SQL, which is applied to many types of databases.
NoSQL is a design-based database with popular implementations.
MySQL uses a standard query language (SQL).
NoSQL does not use a standard query language.
MySQL has many excellent reporting tools.
NoSQL has a few reporting tools that are difficult to standardize.
MySQL can encounter performance issues with large datasets.
NoSQL provides excellent performance with large datasets.
Think 8base
In the company , where I work, we provide each project's workspace with a relational database Aurora MySQL hosted on AWS. While NoSQL is a logical choice when your application's requirements demand high performance and scalability, we believe that strict data consistency ensured by a DBMS is essential when developing SaaS applications and other business software.
For startups and developers creating business applications that require reporting, transaction integrity, and clearly 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
