Database migration might be a sensitive task to handle, especially when it comes to the development of the database for the online marketplace project. If you have already looked for what the best way to convert SQL to MongoDB is, then there must be solid reasons for this move. Let’s start from defining the difference between the document-oriented database and the relational database.
Document-oriented database vs relational database
Difference between two types of databases
What is a relational database?
A database that is based around relational model of data and, if to take into consideration the ‘visual’ explanation of such database, is represented in tables, columns and rows that relate to them. Relational database requires a pre-defined pattern (schema), according to which you can put the data pieces in such database type. You create the specific table and name it according to the data type such base is going to store. In the row section in the table, there is usually a single item term. Column in the table represents the item’s attributes (i.e. characteristics, supplementary data for the item).
What’s great about this database type is that it’s well-organized being represented in the table.
But when it comes to the more complicated data organization, if such database is not properly organized, it might cause troubles.
Consider the example. You’ve got a large database that is represented in various tables with numerous relations between each other. You’d like to update information in any segment of any table, thus you need to make sure whether the related information is going to update in other interconnected tables and their segments. To maintain a proper care of your database as it grows, you should think about its structure that works best for your marketplace project right from the beginning. Otherwise, any change threats to cause trivial-to-critical bugs. Apart from this, if your database grew more complex, and you’d like to share it across different servers, there might be some troubles.
Among world-known relational database management systems are (apart from MySQL):
Oracle database - multi-model database type (supports not only relational database type but also document, graph or key-model DBs) by Oracle Corporation
Microsoft SQL Server - database management product by Microsoft, that works on Windows, Linux and Docker containers with such languages, as T-SQL, Java, C/C++, C#/VB.NET, PHP, Node.js, Python, Ruby. It allows to create data for applications and place them in private or public cloud
PostgreSQL - open-source database management system, released by PostgreSQL Global Development Group in 1996
IBM DB2 - IBM-developed RDBMS for Windows, Linux and Unix, first released in 1983
Amazon Aurora - MySQL-based cloud-oriented relational database
If you’re utilizing the power of these, you might be interested to check out Amazon RDS - provides with opportunity to use relational databases by different producers (Amazon Aurora, PostgreSQL, MySQL, MariaDB, Oracle, and Microsoft SQL Server) in the cloud.Now, let’s clarify what document-oriented database types are
This database model is the collection of documents that contain all the necessary data records. You can customize the information written in the documents, group them and create database hierarchy managing documents instead of tables.
Such DBs are quite flexible. It might be of use when you need simpler data organization. The main difference from the relational database is that the document-oriented database uses a key (data item id) and a value (data item description) to structurize data. Apart from this, you can store some info in one document, instead of dividing it into several tables. Later we'll describe when to use MongoDB instead of MySQL.
Source: Document-oriented Databases [PDF] .
The drawback of such databases lies in that it’s difficult to sort out the data that requires strict organization and structure. The databases of such type are well-suited for analytics collection, logging and storing the info that is possible to keep as a document.
See how Fabio Fumarola describes use cases for NoSQL databases
Check the original presentation here:
Document oriented database management systems
Update: check out MongoDB's paper on MongoDB & MySQL comparison.
Why might I need such migration? When to use MongoDB instead of MySQL?
Migration from SQL to Mongo gives you the opportunity to take advantage of the following major features a document-oriented database can provide:
1) Faster organization and access to simple data which can be documented
2) Unstructured information storage (in some cases it is useful)
3) Implementation of distributed access to the data which is hosted on multiple servers
Road to how to migrate MySQL database to MongoDB and post-migration steps
In order to start the migration from MySQL to MongoDB, you need to understand whether it is really necessary for your project. You should know that when the database structure is very complex, and the whole data retrieving logic works with the help of sending the direct queries to the SQL database, such migration will be complex, long-lasting and as a consequence, far from cheap.
MongoDB might seem not that quick-to-act, when there’s a complex collections structure. One of the significant MongoDB tips and tricks on performance is to split a complex table in MySQL into several "flat collections" in MongoDB.
The perfect scenario and the best way to convert SQL to MongoDB is the product’s complete move to the brand new technology.
For example (php-framework + MySQL ==> js-framework + Mongodb).
In general, we believe that you can use any kind of database, no matter whether it is MongoDB or MySQL. What really matters, is to properly choose the solution depending on project’s purpose. Then you should analyze and build the structure of the chosen database with competence and responsibility.
MySql to MongoBD migration. Steps to do after the move
After the migration from MySql to MongoDB, it's recommended to perform database indexing, which will allow you to get data from a large number of documents much faster.
The next thing is to monitor the performance and security of your marketplace project or an app that uses MongoDB. You should be able to compare document-oriented database vs relational database and see if the use of the document-oriented one is justified.
Mongo DB has released an entire white paper on RDBMS to MongoDB Migration Guide with details on how to migrate from MySQL to MongoDB.
The most important sequence of actions highlighted in the guide is:
You can think of it, as the best way to convert SQL to MongoDB.
Mongo DB app examples
Among the most prominent MongoDB customers there are Google, Cisco, Facebook, EA, Adobe, SAP and other well-known brands, displayed in MongoDB’s full list of customers .
At Apiko, we’ve been working with MongoDB for a quite long time and have helped our customers to carry out large projects using MongoDB best practices. We’ve got experience in the implementation of this database in various projects, from food marketplaces to task management systems and data visualization tools.
The vision behind the Plio project is a collaborative software tool designed to apply lean management approach to streamline the compliance management process.
The initial goal was to design a platform (PlioHub) that:
Can be configured quickly to support any set of compliance management processes
Runs in the cloud, enabling on-demand provisioning and low entry cost
Offers simple, very intuitive and integrated user experience.
The main features:
We've had to create MongoDB database here to edit a large number of documents that contain numerous data fields of different types.
Databazel is a simple tool for data visualization directly from MongoDB. It allows to stay in control of your data, effortlessly and quickly analyze it, and share reports with your colleagues. This is another case on when to use MongoDB instead of MySQL.
Get a quick video overview of concept behind Databazel and its functionality:
Build a chart of a chosen type (Bar, Line, Pie, Scatter, Radar, Polar, Donut)
Easily customized dashboard
Ability to check the SQL-like query to MongoDB
As you can see, database type choice depends on the specifics of the online marketplace app project you’re planning. When comparing document oriented database vs relational database you should pay a meticulous attention to the pains and gains of both database management system types. You need to learn in details, when to use MongoDB instead of MySQL and if there is a strong reason to start google for how to migrate MySQL database to MongoDB.