MySQL to MongoDB - What’s the Difference?

Foreword: This post was done as part of a technical blog assignment in which we were to learn about the differences between a relational and an non-relational database (MySQL & MongoDB are what I chose). With that said, please do leave your feedback and thoughts below! — Thank you

Relational databases, non-relational databases, what’s the difference a database is a database, right? Ehh, no. While the underlying principal of a database may remain a constant, a means to store data in a structured manner, the core structure in itself is quiet different between the two.

For the purpose of this writing the two databases we’ll be comparing and contrasting are MySQL, a relational database, and MongoDB, a non-relational database.

Language & Schema

First and foremost, relational databases like MySQL use a language called SQL (Structured Query Language) to interface with the database. Non-relational databases, like MongoDB, don’t use SQL to interface with the database. MongoDB, for example uses it’s own query language that is relatable to SQL, but used more like your standard object in JavaScript.

When it comes to schemas, or database structure, MySQL and MongoDB couldn’t be different. MySQL requires a schema to be defined before you can start putting any data into it. Changing your schema once you’ve started putting data into it is also a nightmare! MongoDB on the other hand doesn’t require any schema to be setup before you can start storing data within it, and is relatively straightforward when it comes to remodeling the schema layout. In addition, MySQL requires all your rows within a table to hold the same columns, while MongoDB doesn’t require all it’s documents in a collection to hold the same fields.

Normalization

MySQL performs best when the data within it is normalized, meaning it has non-repeating data groups that are not redundant and are dependent on the primary key. In a nut shell, you want your data spread throughout multiple tables, and for the data within the tables to hold useful data that isn’t repetitive. With MongoDB, all the above could get thrown out the window because it doesn’t really matter to the core functionality of the database, and duplicating data is pretty common.

“Documents in a collection need not have an identical set of fields and denormalization of data is common.” — MongoDB Documentation

Core Terminology

With any language terminology is important to know, and that’s no different with database languages. Thankfully most of the terms used with MySQL and MongoDB can easily be explained at the same time, and even flip flopped. The main gotcha here is that MySQL strictly uses one set and MongoDB uses the other. As I list through these, note that in the headings MySQL’s terms are on the left and MongoDB’s terms are on the right. Another useful visual is to imagine the structure as an Excel Spreadsheet.

Tables / Collections

Tables are basically a collection of specific data, like drinks for example. So we might have a table that holds information specific to drinks, like the name of the drink, who it’s manufacturer is, and maybe how many we have in stock

In an Excel document, a table or collection would be an individual spreadsheet. It’s meant to hold one group of related information.

Schema:

Table/Collection: TDrinks

Columns / Fields

Columns are the fields we’re going to divide our information up into within our table / collection. So in our above example an id, drink name, manufacturer, and our in stock count would be our columns or fields. The id is important as it gives us a way to programmatically reference that single instance of information.

In an Excel document, columns or fields would be the same as columns in a spreadsheet. They divide our related data into different sections of information.

Schema:

Table/Collection: TDrinks 
Columns/Fields: intDrinkID, strName, strManufacturer, intStockCount

Row / Document

A row or document is a single item within our table. An example of this would be a single drink that fills out all our column information. So, our id would be 1, Dasani could be our drink name, it’s manufacturer is Coca-Cola, and I currently don’t have any in stock.

In an Excel document a row or document would be an individual cell of data within our spreadsheet. This may be a restrictive analogy for a MongoDB document, but if you’re starting out it may help to visualize it as so.

Schema:

Table/Collection: TDrinks
Columns/Fields: intDrinkID, strName, strManufacturer, intStockCount
Row/Document: 1, ‘Dasani’, ‘Coca-Cola’, 0

Join / Embedded Documents

This topic needs to be separated out by by database type, so we’ll first start with MySQL. A join in MySQL is how we combine our tables back together to make multiple segments of information appear as one. So if we normalized our example above we would want to cut manufacturer out into it’s own table. This would optimize our database schema for a relational database. We would also want to create a foreign key on the intManufacturerID columns. So our schema would become:

In an Excel document a join query would be similar to taking two already existing spreadsheets and creating a third that is only built of data from the two already existing spreadsheets.

Schema:

Table: TDrinks
Columns: intDrinkID, strName, intManufacturerID, inStockCount
Row: 1, ‘Dasani’, 1, 0
Table: TManufacturers
Columns: intManufacturerID, strName
Row: 1, ‘Coca-Cola’
Foreign Key: TDrinks.intManufacturerID 
-> TManufacturers.intManufacturerID

We would then use a join to combine the data within the two tables above back into one when we go to send the data to our application from the database.

Since we don’t want to normalize our data within MongoDB, we would skip the above steps completely, and remain with our simple schema:

Collection: TDrinks
Fields: intDrinkID, strName, strManufacturer, inStockCount
Document: 1, ‘Dasani’, ‘Coca-Cola’, 0

Embedded documents in MongoDB is just a way to combine two documents into one, instead of creating two separate collections. This is one cool part about MongoDB, we can store objects and arrays within our data which is what allows us to create embedded documents!

So if for some reason Dasani had two manufacturers and we needed to also store information on the manufacturers we could simply change it to an array with an object inside and add another manufacturer at the next array index position.

Collection: TDrinks
Fields: intDrinkID, strName, strManufacturer, inStockCount
Document: 1, ‘Dasani’,
[
{strName:‘Coca-Cola’, strColor:'red'},
{strName:'Pepsi', strColor:'blue'}
], 0

With MySQL this step would require yet another table so that we can separate our list of manufacturers and our list of drinks completely and put the data that combines the two into a relationship table. We would then need a foreign key going from the relationship table to the drinks table and the relationship table to the manufacturers table. This would end up looking like this:

Table: TDrinks
Columns: intDrinkID, strName, intManufacturerID, inStockCount
Row: 1, ‘Dasani’, 1, 0

Table: TManufacturers
Columns: intManufacturerID, strName, strColor
Row: 1, ‘Coca-Cola’, 'red'
Row: 2, 'Pepsi', 'blue'

Table: TDrinkManufacturers
Columns: intDrinkID, intManufacturerID, intSortOrder
Row: 1, 1, 1
Row: 1, 2, 2
Foreign Key: TDrinkManufacturers.intDrinkID 
-> TDrinkManufacturers.intDrinkID
Foreign Key: TDrinkManufacturers.intManufacturerID
-> TManufacturers.intManufacturerID

So things are definitely a lot more structured with MySQL, but they’re also a lot more complex.

When To Use MySQL vs MongoDB

While you can normalize or denormalize data to accommodate a certain style of database, there is a line to draw with this. If you find most of your tables are going to require relationship tables just to allow many-to-many relationships in an organized manner, you may be better off sticking to MongoDB so you can embed your documents into one another. While on the other hand, if you can easily normalize your data or if you need normalized and structured data, MySQL is the way to go.

MySQL would also be the way to go if you’re handling a high volume of database requests, require either transactions or race conditions, and can predefine a set, non-changing, schema since changing the schema after data is stored is complex and unrecommended.

MongoDB would be the way to go if you’re handling complex data types, inconsistent data that may lead to a varying schema, or if you can’t predefine a set, non-changing, schema since it allows you to easily alter the schema layout.

Summary

Both MySQL and MongoDB offer a wide array of benefits and shortcomings, though neither is a bad option for the typical project. Both databases are dependable and can quickly operate if used correctly. They both may come with a learning curve, but because MongoDB’s query language is more object based than MySQL’s SQL it is often thought of as easier to pickup for developers.

Resources

MongoDB Documentation

MongoDB — MongoDB and MySQL Compared

NeonRain — MySQL vs. MongoDB…

OpenMyMind.net — Multiple Collections Versus Nested Documents

One clap, two clap, three clap, forty?

By clapping more or less, you can signal to us which stories really stand out.