MySQL vs MongoDB: Key Differences, Decision Table and Data Models

MySQL wins when the data has a fixed shape and the server has to enforce integrity: fixed columns, foreign keys, ACID transactions on every write. MongoDB wins when the application reads one aggregate at a time and the shape keeps changing: nested documents, no ALTER TABLE, one read instead of a join. DbSchema diagrams both, from the same desktop application.

This comparison is version-qualified. It is checked against MySQL 9.7 LTS[1], the current long-term-support release, and MongoDB 8.3[2], the version the MongoDB Database Manual marks as current. Both products have moved a long way past the stock comparisons that still circulate. MongoDB has had multi-document transactions since 4.0[3]. MySQL adds and drops columns instantly[4].

DbSchema is not a database. It connects to your MySQL[5] or MongoDB server[6] and reverse-engineers what is actually there: tables, columns and foreign keys on the relational side, collections and inferred fields on the document side. You then work on a diagram instead of a shell transcript.

Key Differences Between MySQL Tables and MongoDB Documents

The shortest version of the difference is the unit of storage. MySQL stores a row in a table whose columns are fixed in advance. MongoDB stores a document in a collection, and the document carries its own field names and types with it.

MySQL vs MongoDB: Decision Table

The decision table below compares the two on the things that actually decide the choice. Every row is taken from the current MySQL and MongoDB reference manuals rather than from a general impression. Read it as a set of trade-offs. Most rows have a job for which each answer is the right one.

Two rows decide most projects. If more than one entity has to stay consistent inside a single write, MySQL and InnoDB do that work for you. If the application almost always reads one parent together with its children, MongoDB turns that into a single document read and the join disappears.

Relational Modeling: MySQL Example

MySQL wants the structure first. Define the sql table, its columns and its types, then insert rows that match. Relationships are declared with foreign keys and the server enforces them: foreign_key_checks is enabled by default[10] and rejects a child row whose parent does not exist.

Schema Design for Countries and Cities

The worked example uses the same two entities in both databases, countries and cities.

• Countries: one row per country, with a primary key.
• Cities: one row per city, linked to its country through a country_id foreign key.

SQL Example

CREATE TABLE Countries (
    id         INT PRIMARY KEY,
    name       VARCHAR(200),
    population INT
);

CREATE TABLE Cities (
    id         INT PRIMARY KEY,
    name       VARCHAR(200),
    population INT,
    country_id INT,
    FOREIGN KEY (country_id) REFERENCES Countries(id)
);

That structure lets you normalize your data, link each city to its country, and have InnoDB reject a city whose country_id points at nothing.

The catch is visibility. A CREATE TABLE script tells you nothing about a schema you did not write yourself. Shell clients show data, not design, so a hundred-table database stays a list of names until something draws it.

Document Modeling: MongoDB Example

MongoDB stores collections of documents, BSON objects that can nest other objects and arrays. You insert a document that already contains its data, and the shape of that document is the structure. Modeling countries and cities gives you two options.

Embedded Documents

Embedded documents put the cities inside the country document, as an array field.

db.countries.insertOne({
  "_id": 1,
  "name": "France",
  "population": 67000000,
  "cities": [
    { "name": "Paris", "population": 2148000 },
    { "name": "Lyon",  "population": 515695 }
  ]
})

Embedding fits three situations.

• Cities are only ever read together with their country.
• You never query cities on their own.
• The array has a ceiling you control, so the document cannot grow without bounds[12].

Referenced Collections

Referenced collections keep countries and cities apart, and each city carries a country_id pointing back at its country. Reading both then costs a second query, or a $lookup stage that can only join collections in the same database[9].

db.countries.insertOne({
  "_id": 1,
  "name": "France",
  "population": 67000000
})

db.cities.insertOne({
  "_id": 101,
  "name": "Paris",
  "population": 2148000,
  "country_id": 1
})

Referencing fits the opposite case.

• The child side has high cardinality[12], so the embedded array would never stop growing.
• Cities are written on their own hot path, at different times from countries.
• Cities are queried and managed independently of any country.

Embed or Reference a One-to-One Relationship

One-to-one is the case where the answer is nearly always embed. A country has one capital city, an order has one shipping address, a user has one profile. The rule of thumb: embed the child in the parent unless the child is read without the parent, written on its own hot path, or big enough to threaten the document size limit.

The reasoning is the read pattern, not the cardinality. When the application mostly reads the parent, embedding turns two round trips into one. MongoDB states the rule plainly: structure your schema so the application receives all of its required information in a single read operation[11]. A reference costs a second query or a $lookup, and $lookup runs inside an aggregation pipeline against one database, which is harder to cache and harder to shard.

// One-to-one: the capital lives inside the country document
db.countries.insertOne({
  "_id": 1,
  "name": "France",
  "population": 67000000,
  "capital": {
    "name": "Paris",
    "population": 2148000,
    "area_km2": 105
  }
})

// One read returns the country and its capital together
db.countries.findOne({ "_id": 1 })

Three things break the rule, and all three are about the child document rather than about the relationship.

• The child is read on its own. A profile screen that never loads the parent user makes a second collection worth the extra query.
• The child is written far more often than the parent. Separate documents keep a hot counter from rewriting a cold parent on every update.
• The child is large. A document cannot exceed 16 mebibytes[15] and nesting stops at 100 levels, so a blob-shaped child belongs in its own collection or in GridFS.

MySQL expresses the same one-to-one differently. A child table, a FOREIGN KEY back to the parent, and a UNIQUE index on the referencing column[10], which is the part that turns one-to-many into one-to-one. The read then costs a join every time. That is the price of having the server enforce the relationship instead of the application.

Both Models Side by Side in DbSchema

DbSchema draws the relational model and the document model in the same application, which is what makes this comparison concrete rather than theoretical. Open the MySQL connection in one model and the MongoDB connection in another, and both diagrams come out of the same layout engine, with the same navigation and the same export.

The MySQL Model: SQL Editor or Diagram Editor

Two routes lead to the same MySQL diagram.

• Write CREATE TABLE in the SQL editor and run it. The diagram redraws with the new tables and the foreign key line between them.
• Or add both tables on the canvas, pick column types from dropdowns, and drag a line from Cities.country_id to Countries.id. DbSchema generates the SQL script and can deploy it to the database.

Both routes end in the same place: an ER diagram you can lay out by hand, and a MySQL model you can also reverse-engineer out of a database somebody else built.

The MongoDB Model: Mongo Query or Diagram Editor

MongoDB declares no schema, so DbSchema builds one. It introspects a configurable sample of documents per collection and infers field names, BSON types, nested objects and arrays. The result is an approximation of what the documents actually contain. It is not a schema MongoDB itself enforces[2].

Where a collection carries a validation rule, that rule wins. DbSchema reverse-engineers the rule as the authoritative structure instead of the sampled approximation. Create or edit a collection in DbSchema and it writes the validation rule back to both the database and the local model file.

MongoDB does not declare a foreign key between countries and cities, and it does not enforce one. DbSchema adds a virtual relation instead. Drag cities.country_id onto countries._id and the link is drawn as a connector on the diagram and saved in the model file. It is a DbSchema-side link. The database is not checking it.

Those virtual relations are what makes the data browsable. The Relational Data Editor opens several collections side by side over them, and selecting a country in the parent pane instantly refilters every child pane to the documents whose field values match, cascading as many levels deep as you need.

Two routes lead to the MongoDB diagram as well. Run the insertOne code in the Mongo Query panel and the collection appears on the canvas. Or build it in the diagram editor: create Countries, add fields, choose whether cities is an embedded array or its own collection, and set validation rules without writing a line of JSON.

The exported interactive HTML5 documentation carries a vector diagram image in which collection and field comments are readable as mouse-over tooltips. That export, and saving the model to a file at all, is a Pro feature. Connecting, reverse-engineering and the interactive diagrams are in Community.

Why Use a Visual Tool Like DbSchema?

Neither database shows you its own shape. MySQL gives you SHOW CREATE TABLE, one table at a time. MongoDB gives you a sample document and leaves the rest to you. DbSchema turns both into a diagram you can read. Other tools cover parts of that chain; it is the combination of inferred schema, validation rules, virtual relations, multi-collection browsing and HTML5 documentation that is worth having in one place.

• Design visually. Create tables or collections by clicking and dragging, which Community covers.
• Read the structure. The diagram shows how everything connects, including the virtual relations MongoDB never declares.
• Skip the syntax. DbSchema writes the SQL or the JSON, so you spend the time on the model.
• Version the model. The model file is XML and reviews cleanly in Git; saving the model to a file is a Pro feature.
• Document the schema. Export interactive HTML5, PDF or Markdown documentation, also Pro.

What you should remember

The difference between MySQL and MongoDB is where the shape lives. In MySQL the shape lives in the schema and the server enforces it on every write. In MongoDB the shape lives in the documents and the application owns it.

For a one-to-one relationship read through the parent, embed. For anything that has to stay consistent across entities inside one write, use MySQL and let InnoDB do it. Most real systems end up running both, which is the reason to model them in one place instead of two.

Download DbSchema and point it at your own MySQL or MongoDB instance. Community covers connecting, reverse-engineering and the interactive diagrams. Pro adds saving the model to a file, schema synchronization, the Relational Data Editor and the HTML5 documentation export.

Sources

1. MySQL release and support calendar
2. MongoDB 8.3 Database Manual: Schema Validation
3. MongoDB 8.3 Database Manual: Transactions
4. MySQL 9.7 Reference Manual: Online DDL Operations
5. MySQL
6. MongoDB
7. MySQL 9.7 Reference Manual: Server SQL Modes
8. MongoDB 8.3 Database Manual: Handle Invalid Documents
9. MongoDB 8.3 Database Manual: $lookup
10. MySQL 9.7 Reference Manual: FOREIGN KEY Constraints
11. MongoDB 8.3 Database Manual: Model One-to-One Relationships with Embedded Documents
12. MongoDB 8.3 Database Manual: Embedding vs. References
13. MySQL 9.7 Reference Manual: InnoDB and the ACID Model
14. MongoDB 8.3 Database Manual: Designing Your Schema
15. MongoDB 8.3 Database Manual: Limits and Thresholds