Back to all posts

Schema Design in SQL vs NoSQL: A Visual Comparison

For the architect choosing between a relational database and MongoDB for a new application, and how the schema is designed on each.

On this page

You are planning a new application, and the shortlist has PostgreSQL on one side and MongoDB on the other. In PostgreSQL the database holds the schema and rejects any row that does not fit it. MongoDB, by default, stores any document it receives, and the schema lives in your application code.

PostgreSQL checks each write against the schema in its catalog and stores the row or rejects it with an error; MongoDB stores the document as sent while the application holds the rules

Choose PostgreSQL when several systems read the same data, such as orders joined by reports or payments that must balance. Choose MongoDB when the application reads and writes each record whole and rarely joins it. One system can use both, chosen per table or per collection.

SQL vs NoSQL schema design at a glance

A schema says what you can store, how the pieces refer to each other, and which rules the data follows. A good one keeps each fact in one place and makes the everyday queries cheap, on either kind of database. The difference is whether the database stops you when you break the rules.

SQL means the relational family. NoSQL covers the document, key-value, wide-column and graph families, such as MongoDB, Redis, Cassandra and Neo4j. Below, PostgreSQL 18 stands for the relational family and MongoDB for the document family:

Relational (PostgreSQL)Document (MongoDB)
Schema definedbefore the first row, the same for every rowby each document, unless a validation rule is set
Rules checked bythe engine, on every writethe application, or a validation rule
Relationshipsforeign keysembedded documents, or ids the application resolves
Joinsin the engine, across tablesnone, or a $lookup stage
Atomic unitany set of rows, in one transactionone document
Duplicationremoved by normalizationaccepted where it saves a read
Adding a fieldALTER TABLE, a metadata change for a constant defaultnew documents carry it, old ones do not
Growing past one servera bigger server, read-only standbyssharding, built into the engine
Query languageSQLMongoDB Query API

Relationships and the atomic unit decide most of the design. A foreign key is a rule that PostgreSQL checks on every write. An id stored in a document is a convention that your code has to keep.

A write to one MongoDB document is atomic. Multi-document transactions exist, but MongoDB says that in most cases a multi-document transaction costs more than a single-document write, and does not replace schema design. So a MongoDB design puts data that changes together into one document. A PostgreSQL design spreads it over as many tables as the rules need.

MongoDB shards a collection across machines by a shard key, as part of the engine. A PostgreSQL database grows on a bigger server, and copies of it on standby servers answer read-only queries.

Where the schema lives in PostgreSQL

In PostgreSQL nothing can be stored until its table exists. You define the tables, columns, types and rules first, and then build the application on them. Here is the schema of a small movie review application:

CREATE TABLE users (
  user_id int PRIMARY KEY,
  name    text NOT NULL
);

CREATE TABLE movies (
  movie_id int PRIMARY KEY,
  title    text NOT NULL
);

CREATE TABLE reviews (
  review_id int PRIMARY KEY,
  user_id   int NOT NULL REFERENCES users (user_id),
  movie_id  int NOT NULL REFERENCES movies (movie_id),
  rating    int NOT NULL CHECK (rating BETWEEN 1 AND 5),
  comment   text
);

INSERT INTO users  VALUES (1, 'Ada');
INSERT INTO movies VALUES (101, 'The Third Man');

PostgreSQL rejects a review by a user who does not exist:

INSERT INTO reviews VALUES (1, 99, 101, 5, 'Enjoyed it');
ERROR:  insert or update on table "reviews" violates foreign key constraint "reviews_user_id_fkey"
DETAIL:  Key (user_id)=(99) is not present in table "users".

It also refuses to delete a user who still has reviews. The foreign key's default action is NO ACTION, which fails the delete (PostgreSQL 18 constraints):

INSERT INTO reviews VALUES (1, 1, 101, 5, 'Enjoyed it');
DELETE FROM users WHERE user_id = 1;
ERROR:  update or delete on table "users" violates foreign key constraint "reviews_user_id_fkey" on table "reviews"
DETAIL:  Key (user_id)=(1) is still referenced from table "reviews".

Settle the design before the first row goes in. Each later change to the schema runs against tables full of data, on every environment. The relational side is covered step by step in relational database schema design.

Where the schema lives in MongoDB

In MongoDB you can write data before any structure exists. The application inserts a document, and MongoDB creates the collection at that first insert (databases and collections).

Here are two reviews that PostgreSQL would reject:

db.users.insertOne({ _id: 1, name: "Ada" })
db.reviews.insertOne({ _id: 10, user_id: 99, movie_id: 101, rating: 5, comment: "Enjoyed it" })
db.reviews.insertOne({ _id: 11, userId: 1, movie: "The Third Man", stars: "5" })

MongoDB stores both reviews. User 99 does not exist, and the second review has other field names and a string for its rating.

To read a review with its author, you add a $lookup stage, MongoDB's left outer join. Here it finds no author:

db.reviews.aggregate([
  { $match: { _id: 10 } },
  { $lookup: { from: "users", localField: "user_id", foreignField: "_id", as: "author" } }
])
[
  {
    _id: 10,
    user_id: 99,
    movie_id: 101,
    rating: 5,
    comment: 'Enjoyed it',
    author: []
  }
]

Deleting a user succeeds too, and the user's reviews keep an id that points at nothing. Code that reads reviews has to handle a missing author, and a review of another shape.

Embedding, MongoDB's other option

MongoDB can also store related data inside the parent document:

db.movies.insertOne({
  _id: 101,
  title: "The Third Man",
  reviews: [
    { user_name: "Ada",   rating: 5, comment: "Enjoyed it" },
    { user_name: "Grace", rating: 4, comment: "Well written" }
  ]
})

One read returns the movie with its reviews. A write that changes both is atomic, because it touches one document. The cost is duplication: Ada's name sits in every review she wrote, and a change of name has to update every copy. A document is also limited to 16 mebibytes (MongoDB limits), so a list that grows without bound belongs in its own collection.

MongoDB's guide to embedding and references gives the criteria. By them, a movie's reviews belong in their own collection after all, because they grow without bound on a popular movie:

Related dataRead with the parentWritten on its ownGrows without boundStore it
a movie's genresyesnonoembedded
a movie's reviewsyesyesyes, on a popular moviereferenced
a user's profilenoyesnoreferenced

What happens when the requirement changes

Suppose reviews need a spoiler flag. In PostgreSQL 18 that is one statement:

ALTER TABLE reviews ADD COLUMN spoiler boolean DEFAULT false;

With a constant default, PostgreSQL stores the value in the table's metadata and rewrites no rows. The ALTER TABLE documentation calls this very fast even on large tables. A volatile default, a stored generated column, an identity column or a domain type with constraints rewrites the table and its indexes. With or without a rewrite, ALTER TABLE is a migration that you review and then apply to each environment.

In MongoDB nothing has to run first. New reviews carry the field, and old ones do not:

PostgreSQL rows read the default false from the catalog without being rewritten, while old MongoDB documents lack the spoiler field and only the new one carries it

Until you backfill the old documents, every reader has to treat a missing spoiler as false. The backfill is one more statement to run:

db.reviews.updateMany({ spoiler: { $exists: false } }, { $set: { spoiler: false } })

So the migration does not disappear. It moves into the code that reads reviews, or into an update you run later.

Holding a MongoDB collection to a structure

MongoDB checks documents as they are written when the collection has a validation rule, written as a $jsonSchema:

db.reviews.drop()
db.createCollection("reviews", {
  validator: {
    $jsonSchema: {
      bsonType: "object",
      required: ["user_id", "movie_id", "rating"],
      properties: {
        rating: { bsonType: "number", minimum: 1, maximum: 5 }
      }
    }
  }
})
db.reviews.insertOne({ user_id: 1, movie_id: 101, stars: 5 })

The insert has no rating, so MongoDB rejects it with MongoServerError: Document failed validation.

The collection's validationLevel decides which writes MongoDB checks. The default, strict, checks every insert and update. With moderate, MongoDB skips the check on updates to documents that already broke the rule (validation levels). So you can add a rule to a collection that still holds documents written before the rule.

A validation rule checks one document at a time. It cannot check that user 1 exists in users, so that rule stays in the application.

The same data as a SQL diagram and a MongoDB diagram

DbSchema draws both kinds of schema from the database rather than from a picture you maintain by hand. Connected to PostgreSQL, DbSchema reverse-engineers the catalog and draws the tables, the columns and the foreign keys. Here are the three tables from the schema above:

The users, movies and reviews tables reverse-engineered into a DbSchema diagram, with foreign keys from reviews to users and to movies

Connected to MongoDB, DbSchema reads 100 documents from the start and 100 from the end of each collection. From them it infers the field names, their types, and the nested objects and arrays. The diagram shows what those documents contain. That field list is not a schema that MongoDB enforces. Where a collection has a $jsonSchema validator, DbSchema reads the fields from the validator instead (MongoDB in DbSchema).

A MongoDB database shop as a diagram in DbSchema: customers with the nested document address, orders with the array items, and products, joined by relations

MongoDB declares no relations, so DbSchema draws a relation where a field holds the _id of a document in another collection, such as orders.customer_id in the screenshot. To add a relation that DbSchema missed, such as a review's user_id, drag the field onto the field it refers to. Such a relation is virtual: DbSchema keeps it in its model file, and MongoDB never sees it.

The Data Explorer follows the relations. Select a customer, and the pane beside it shows that customer's orders. DbSchema also exports the diagram as HTML5 documentation: a vector image of the diagram, with the comments as mouse-over tooltips. There are worked examples for MongoDB and for MySQL.

Download DbSchema, connect to the database you already have, relational or MongoDB, and reverse-engineer it into a diagram. Connecting, reverse-engineering and the diagrams are in the free Community Edition, which also documents a diagram of up to 12 tables. Saving the model file, documenting larger diagrams and the Data Explorer are in Pro.

Model SQL and MongoDB in the same diagram

DbSchema reverse-engineers relational databases and MongoDB alike, samples your documents to derive a field list per collection, and draws both as interactive ER diagrams. The Community Edition is free.