Often the question is MySQL or MongoDB!
So, let’s look at when you might want to choose which database and what are the differences.
This quick article should help you to choose SQL or No SQL database in your next web project or whatever your next project is.
How both the database system works?
SQL based MySQL is a Relational Database Management System (RDBMS).
You write queries with the help of SQL (Structured query language).
RDBMS based database works on certain assumptions and requirements so you design your database by following normalization rules. Better the rules you follow better structured data you will find in your database.
Database works with tables so for example if we want to store products of our app we can create a product table to store the data. So, tables are data storage container in MySQL.
You have to be precise with the schema of the table which defines what type of data can be stored in the table. The schema is defined by fields or we call them columns.
So, our product table may have fields like ID, Name, Price, and Description.
Every new entry we make to the table a new row will be added in it.
Once table is defined in the database then it can’t have more fields. So the product table record can have price and description along with name. But if you want tax to be saved for any record then you can’t have that in this table schema as there is no such field to store it. So, all the records in the table must adhere to the defined schema. When we put data into such a table we therefore have to normalize it which means we have to ensure that however we are fetching the data and whichever extra data we might have or whichever data might be missing that we bring it into a format which fits into that table.
The important building block of a sequel world is that you typically don't only work with one table but instead with multiple tables which are related so we could have users table to store users’ data and then orders table to store typically the combination of users and products that are bought.
In RDBMS, tables are connected with relationships where we use the primary key and foreign keys to retrieve data in an efficient manner with the help of SQL joins.
There are multiple types of relationships,
sequel it's about use key characteristics RDBMS and builds strong schema with the relational nature of the data we store distributed across multiple tables which we then connect through relations and the structured query language is capable of querying these relations. There are special commands so called joins which you can use to retrieve the data in one result set even if it is stored in multiple tables. Obviously, the more complex the relationship is and the more tables are included the longer such an operation can take but it is possible and it is one of the core strengths of the sequel world.
Let’s look at NoSQL world!
There are different NoSQL databases present like MongoDB, Hadoop, Casandra and many more. I’ll have a look at MongoDB in this article since it is a similarly popular no sequel database.
The name MongoDB is simply stemming from the word humongous because it's built to store lots and lots of data in a very efficient way.
In MongoDB, we do have databases but there are no tables. Instead, we have collections. You can translate it with tables so essentially a shop database may have products, users, orders collections.
Like the tables do have rows in Sequel, here collections consist of documents. Documents look different than rows because they are built of JSON (Key & Value pair).
Interestingly, documents don’t have schema so, documents in one collection can have different fields.
Unlike the sequel tables where you have fixed schema where you may have null or empty column values, here in no-sequel, you can have completely different documents in the same collection. You can have few fields different or the entire documents different in the same collection.
There are no relations in the no-sequel world. Of course, you can theoretically relate your collections in your database however MongoDB rely way less on such relations. Instead, the idea is that you put all the information in one place.
So, for an example, you have the collection of your order where each order does not only contain the use IDr and product ID but it contains the key user data you need to work with the order something like the address, email and the ID and the key product data you need like price, product name, and the ID. So whatever you might need to display it on an order page and process it internally you would put that into your collection.
Of course, you might still have an extra user collection and an extra product collection with more detailed data but you don't really need to query that data because you got the data you need in each collection which you might be querying from different points of your application. This is the idea to have less relation merging going on to have super-fast and efficient queries.
Of course, the disadvantage is having duplicate data. If the product name changes you have to update it not only in the product collection but also in the orders collection.
If you have an application where you have a lot of reads and not that many writes for products for then this would be a great set up.
Understanding both the worlds, it's important to understand scaling aspects of both the databases.
Scaling can be horizontal and vertical.
Horizontal scaling demands to add more servers and you need to split your database across these servers.
Horizontal scaling is hard to implement. It is especially very hard with a sequel server because it’s not easy to split data between multiple servers. Thus, horizontal scaling is not supported by sequel databases.
Vertical scaling demands to add more power to your existing server. The obvious downside of it is there will be some limit to add hardware power. Sequel databases support the vertical scaling very well however you need to play with the limitation of the computing power you can add on your server.
The chances are that you may not hit with the scaling limitations unless your application becomes really big however while we are considering the scaling aspects, MongoDB or no-sequel databases supports the horizontal scaling very well. You can easily split your data across multiple servers in MongoDB.
Vertical scaling is of course always possible with even no-sequel databases as well.
Performance is relative word and it depends upon many factors however the most common and important factor to consider is size of data you have.
If you have a tiny to medium size of data then very well structured sequel (MySQL) database can bring very good performance. As especially when you apply indexing you need not to worry about the performance aspects.
However, even with medium size of database, you may need to apply demoralization rules to retrieve data faster from MySQL.
If you have tens of thousands of data where your application performs per second read and write operations and having complex queries with joins then your application may hit to the performance bottle neck in MySQL.
Since no sequel (MongoDB) is schema less, you have an advantage on such applications to get the better performance. Having no schema and merged data is great to read faster. The disadvantage is when you have write operation; you may have to take care of multiple collections since you have duplicate data present in different collections for the same entity.
So, if you have a user data present in 4 collections and you may update it all the time and here we are talking about thousands of write operations then you may even face the performance issues with no sequel (MongoDB).
What is to use?
The question comes is what is to use – more strict sequel (MySQL) or more lose no-sequel (MongoDB)?
The answer depends upon, the kind of application you're building and the kind of data you're storing and in really big applications or businesses you typically use both solutions because you work with different data or with different applications and therefore you have different needs
The advantage with sequel (like MySQL) is that you have defined schema and predictable layout of the data. It is easy to maintain consistency in the data since you maintain just one copy of the data. You use relations and joins to retrieve data from multiple tables.
The downside of sequel is if you have complex queries and if you do lot of reads then you may have worse performance.
The advantage with no-sequel (like MongoDB) is that the data is already merged in the right way in a collection so you don’t need to merge it manually through queries. Since there can be multiple copy of the data, you need to be more cautious while updating any data otherwise you will end up of injecting inconsistency in your solution.
So, you can build any application with either database. There is no clear equation and you will probably only face the issues when your app really become very very big.
No sequel is a hyped database now days. It does offer significant advantage but it’s wrong to day It’s strictly better than sequel databases.
Deployment is another aspect of choosing the right database. Deployment of MySQL is extremely easy and by default provided by any hosting provider whereas MongoDB requires specialized hosting services to use. Not all hosting providers offer MongoDB as default database service on their Linux servers. So you may also need to consider your strength about deploying the solution and cost of hosting services while taking the final decision.
Our dedicated developers’ team for ReactJS, React Native and PHP are well versed with both MySQL and MongoDB.We take the decision based on the size of data and the number of read-write transactions requires per second in any application before we commence the work.
Feel free to Get In Touch To Hire Dedicated Developer in case you wish to know more about any type of database for your project.