Schema Design in SQL vs NoSQL: A Visual Comparison

You are planning a new application, and the shortlist has PostgreSQL on one side and MongoDB on the other. The schema is designed differently on each, because a different party holds it. PostgreSQL keeps the structure in the database and refuses any row that does not fit. MongoDB, by default, stores whatever document it receives, and the structure lives in your application code. Every difference below follows from that.

What schema design means in SQL and NoSQL

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 it.

SQL means the relational family. NoSQL covers several families, and each stores something different:

The comparison below is between the relational and the document family, the two on the shortlist above. The relational side is covered step by step in relational database schema design.

SQL vs NoSQL schema design at a glance

Two rows decide most of the design. A foreign key is a rule that PostgreSQL checks on every write, while an id stored in a document is a convention that your code has to keep. And a write to one MongoDB document is atomic. Multi-document transactions exist, but MongoDB's transactions page says that in most cases they cost more than single-document writes and do not replace schema design. So a MongoDB design puts data that changes together into one document, while a PostgreSQL design spreads it over as many tables as the rules need.

Scaling pulls the other way. MongoDB shards a collection across machines by a shard key, as part of the engine. A PostgreSQL database grows on a bigger server, with read-only replicas for extra reads, and splitting its writes across machines is a project of its own.

How a relational schema comes first

In a relational database nothing can be stored until its table exists, so you define the tables, columns, types and rules first, and build the application on them.

Here is the schema of a small application in PostgreSQL 18:

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');

A review by a user who does not exist is rejected as it is written:

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".

Deleting a user who still has reviews fails too, under the foreign key's default action, NO ACTION (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".

The price comes later. Every change to this structure is a statement run against tables that already hold data, on every environment, in the right order. That is why the design is settled, on a diagram, before the first row goes in.

How a MongoDB collection takes its shape

In MongoDB the order is reversed. The application inserts a document, MongoDB creates the collection at that first insert (databases and collections), and the collection's shape is whatever its documents contain.

Here is the same review as a reference to its user, next to a review written by other code:

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" })

Both reviews are stored, although user 99 does not exist and the second review uses other field names and a string for its rating. By default the documents in a collection share no schema. Reading a review with its author takes a $lookup stage, MongoDB's left outer join, and here it finds nothing:

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 behaves the same way. The delete succeeds, and the user's reviews keep an id that resolves to nothing. Every piece of code that reads a review has to handle an author who is not there, and a review of another shape.

Embedding, MongoDB's other option

The document model's own answer is to store the related data inside the document that reads it:

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, and a write that changes both is atomic, because it touches one document. The price is the copy. Ada's name sits in every review she wrote, and a change of name has to find them all. A document is also limited to 16 mebibytes (MongoDB limits), so a list that grows without bound does not belong inside one.

MongoDB's guide to embedding and references gives the criteria. Applied to the same application:

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, which the ALTER TABLE documentation calls 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 instead. Either way it is a migration, reviewed and then applied to each environment.

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

Until the old documents are backfilled, every reader has to treat a missing spoiler as false, and the backfill is a migration in another form:

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

The work does not disappear. It moves from a migration you plan to a branch in the code, or to 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 validationLevel decides which writes are checked. The default, strict, checks every insert and update. moderate skips updates to documents that already broke the rule, so a rule can be added to a collection that still holds old shapes (validation levels). A validation rule checks one document at a time, so it cannot check that user 1 exists in users. That rule stays in the application.

DbSchema reverse-engineers a collection's validation rule and shows it on the diagram as the collection's structure. Creating or editing a collection in DbSchema writes the rule back to both the database and the model file (MongoDB features).

The same data as a SQL diagram and a MongoDB diagram

DbSchema draws both models from the database rather than from a picture you maintain by hand. Connected to a relational database such as PostgreSQL or MySQL, DbSchema reads the catalog and draws the tables, the columns and the foreign keys.

Connected to MongoDB, DbSchema reads a configurable sample of the documents in each collection and infers the field names, their BSON types, and the nested objects and arrays. The result approximates what the sampled documents contain; it is not a schema that MongoDB enforces.

Links that MongoDB never declares, such as a review's user_id, become virtual relations when you drag one field onto another in DbSchema. DbSchema draws them as connector lines, like the ones from reviews above, and saves them in the model file. The Relational Data Editor in DbSchema follows them to open collections side by side: selecting a user filters the reviews pane to that user's documents, and the filtering cascades through further levels. DbSchema also exports either model as HTML5 documentation, a vector diagram with the collection and field comments as mouse-over tooltips, and the model file can be kept in Git so that a schema change is reviewed like code. There are worked examples for MongoDB and for MySQL.

When to use each type

Choose the relational model when the data has a shape you can name and more than one system reads it. Reports that join, money that has to balance, and a rule you would otherwise repeat in three services are all reasons to let the engine hold the structure. The price is a migration for each change, and it buys a database that cannot be talked into an invalid state.

Choose the document model when the read is the design. A record fetched whole, written whole and rarely joined fits one document better than five tables, and a collection can hold two shapes during a rollout. The price is that every reader carries the rules, and a fact stored twice is updated twice.

One system can use both, and the choice is made per table or per collection rather than once per project.

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. Saving the model file, the HTML5 documentation and the Relational Data Editor are in Pro.