Relational to NoSQL database

Hey Everyone,

Today I’m going to share you the other experience that I’ve struggled with. I know after reading this post most of you will relate your issues with my experience and you will find a new way to deal with them.

The pain of relational database in a small project where you no need of transaction support and also not defined any complex relationship between the multiple entities.  We are very habitual with relation database we always trying to go with them. But here I’m trying to stop you, first check NoSQL, if they not meet your requirement. Then go with relational one. I know that first think clicked in your mind.

“Nikesh, who the hell are you stopping me to meet my first love(Maybe 🙂 )”.

Yes, I agree replacing the first love is pretty difficult but try to do new affair that may make your life cool. 🙂

Let me explain!!

What is NoSQL?

NoSql is basically a database used to manage huge sets of unstructured data, where data is not stored in a tabular relation like a relational database.

Why NoSQL?

A traditional database prefers more predictable and structured data. The technologies are increasing and we want to automate everything, in such situations we want to gather all type of data to be more predictable. Every time getting structural data is a bit difficult. Many times we need to deal with such situations where we need to store unstructured data. Here comes NoSQL as a savior.

NoSQL as per name says NOT ONLY SQL that comes with lots of more added benefit. It provides a completely new way then the Relational DB to deal with data. Managing any type of data is very simple in NoSQL then relational.

Types of NoSQL database

  • Document type: In this database, the key is paired with a complex data structured called as Document. Example: MongoDB
  • Graph stores: This types of a database is usually used to store networked data. Where we can relate data based on some existing data. Example: Neo4J
  • Key-Value stores: These are the simplest NoSql database. In this database each record store with a unique key to identify it. Example: Aerospike
  • Wide column-stores: This type of database store large data set. Example: Cassandra.

Let’s see one of the use cases (using MongoDB and MySQL):

Imagin that we have beer store database that holds beer store basic information, like beer style, beer type, and beer store addresses.

Let’s see a basic schema design in case of the relational database.

Here I have created beer_store table that holds store names then created address table that holds beer store addresses (one store can be available in multiple places)  along with country and city table. then I’ve defined beer_style and beer_type that provided by the beer stores. then defined many to many relationships between beer type and beer_store and the same in case of beer_style and beer store also.

Screen Shot 2018-04-15 at 9.06.53 PM.png

Now same beer store we can achieve in case of NoSQL (JSON-like documents). below schema.

There is two way you can design your NoSQL schema.

  1. You can store all the data into the same collection. but there is some disadvantage when some data repeat multiple times.
  2. You can store a reference to data little bit similar to SQL.

Here to avoiding confusion, I’ve separated few tables where information is fixed and that will repeat in case of another beer store record.

beer_store

Screen Shot 2018-04-15 at 2.47.28 PM.png

and other tables that hold values and references

beer_style

Screen Shot 2018-04-15 at 4.32.42 PM.png

beer_type

Screen Shot 2018-04-15 at 4.33.26 PM.png

country

Screen Shot 2018-04-15 at 2.51.41 PM.png

Query Example SQL VS NoSQL

#SQL

select * from beer_store;
//output
+------------+-------------+
| pkstore_id | name        |
+------------+-------------+
|          1 | Magic Beer  |
|          2 | Beerland    |
|          3 | Uptown Beer |
+------------+-------------+

#NoSQL

db.beer_store.find()
//output
{
	"_id" : ObjectId("5ad31853e5ce6fe732eb7300"),
	"name" : "The Beera",
	"address" : [
		{
			"address1" : "sec 4",
			"address2" : "shastri cercle",
			"city" : "jaipur",
			"country" : "India"
		}
	],
	"beerType" : [
		"Ales",
		"Lagers"
	],
	"beerStyle" : [
		"Amber",
		"Blonde"
	]
}
{
	"_id" : ObjectId("5ad4bfa2b20cf32e1fa3cb6d"),
	"name" : "BeerLand",
	"address" : [
		{
			"address1" : "sec 4",
			"address2" : "hiran magri",
			"city" : "udaipur",
			"country" : "India"
		}
	],
	"beerType" : [
		"Ales",
		"Lagers"
	],
	"beerStyle" : [
		"Amber",
		"Blonde"
	]
}

Above both SQL and NoSQL query requested for beer_store but as per both are different way persisted result return based on it.

Same NoSQL response we can achieve with relation database but for this, we have to do multiple joins, subqueries and inbuilt JSON converter.

Conclusion: Relation database has their own importance like supporting the ACID properties. they are well organized.  But many cases NoSQL appreciable, like when we do search operation, sorting operation filtering on one-to-many and many-to-many relationships. 

Hope you like this. Thanks for reading 🙂

 

Advertisements