Relational vs Document Database

Quick verdict

A relational database stores data in tables. Each fact is kept once, rows refer to each other through keys, and the database enforces those references with foreign key constraints. Getting a complete business object, such as an order with its customer and products, means joining several tables at query time. PostgreSQL, MySQL and SQL Server work this way.

A document database stores data as JSON-like documents grouped into collections. A document can nest objects and arrays, so an order and its lines can live in one record. MongoDB's manual states the core principle as "data that's accessed together should be stored together", and says documents in one collection need not share the same fields. Cloud Firestore describes itself as a NoSQL, document-oriented database whose documents can also hold subcollections.

This page is only about the modelling decision: how the same data is shaped, queried and changed under each model. Product choice, scaling and consistency are covered in SQL vs NoSQL, MongoDB vs PostgreSQL and the article relational database vs NoSQL.

Side by side

Key differences

The same data, modelled both ways

Take a small shop: two customers, three products and two orders, one of which has two lines. In the relational model this becomes four tables. Each customer and product is stored once; the order lines refer to them by key. The unit_price on the order line is a deliberate copy, because the price charged must not change when the list price does.

-- PostgreSQL syntax (also runs on SQLite)
CREATE TABLE customers (
  customer_id int PRIMARY KEY,
  name        varchar(100) NOT NULL,
  email       varchar(200) NOT NULL UNIQUE,
  country     char(2)      NOT NULL
);

CREATE TABLE products (
  product_id  int PRIMARY KEY,
  sku         varchar(20)   NOT NULL UNIQUE,
  name        varchar(100)  NOT NULL,
  list_price  numeric(10,2) NOT NULL
);

CREATE TABLE orders (
  order_id    int PRIMARY KEY,
  customer_id int  NOT NULL REFERENCES customers (customer_id),
  order_date  date NOT NULL,
  status      varchar(20) NOT NULL
);

CREATE TABLE order_lines (
  order_id    int NOT NULL REFERENCES orders (order_id),
  line_no     int NOT NULL,
  product_id  int NOT NULL REFERENCES products (product_id),
  quantity    int NOT NULL CHECK (quantity > 0),
  unit_price  numeric(10,2) NOT NULL,
  PRIMARY KEY (order_id, line_no)
);

INSERT INTO customers VALUES (1, 'Asha Patel', 'asha@example.com', 'GB'),
                             (2, 'Tom Reid',   'tom@example.com',  'IE');
INSERT INTO products  VALUES (1, 'KB-01', 'Keyboard', 45.00),
                             (2, 'MS-02', 'Mouse',    20.00),
                             (3, 'HB-03', 'USB hub',  30.00);
INSERT INTO orders    VALUES (1001, 1, '2026-09-14', 'shipped'),
                             (1002, 2, '2026-09-20', 'pending');
INSERT INTO order_lines VALUES (1001, 1, 1, 1, 45.00),
                               (1001, 2, 2, 2, 20.00),
                               (1002, 1, 3, 1, 30.00);

In the document model, the order is the aggregate the application reads and writes, so it becomes one document in an orders collection. The lines are embedded because they belong to the order, are bounded in number and are always read with it. The customer is referenced by id, with a copy of the fields the order screen needs; the product is referenced by SKU, with its name and price copied at the time of sale. Customers and products would still have their own collections.

// Document in the orders collection (JSON; in MongoDB, order_date
// would normally be stored as a BSON date rather than a string)
{
  "_id": 1001,
  "order_date": "2026-09-14",
  "status": "shipped",
  "customer": {
    "customer_id": 1,
    "name": "Asha Patel",
    "country": "GB"
  },
  "lines": [
    { "sku": "KB-01", "name": "Keyboard", "quantity": 1, "unit_price": 45.00 },
    { "sku": "MS-02", "name": "Mouse",    "quantity": 2, "unit_price": 20.00 }
  ],
  "total": 85.00
}

Notice what moved. The relational design has no copy of the customer's name in the order, and computes the total when asked. The document design stores both, so that one read returns everything the order page shows, and accepts the job of keeping those copies sensible.

One query each way

The question: which shipped orders include a keyboard (SKU KB-01), for which customer, and for how much? The relational query assembles the answer from four tables:

-- PostgreSQL
SELECT o.order_id, c.name, SUM(l.quantity * l.unit_price) AS order_total
FROM   orders o
JOIN   customers   c ON c.customer_id = o.customer_id
JOIN   order_lines l ON l.order_id    = o.order_id
WHERE  o.status = 'shipped'
  AND  EXISTS (SELECT 1
               FROM   order_lines k
               JOIN   products    p ON p.product_id = k.product_id
               WHERE  k.order_id = o.order_id
                 AND  p.sku = 'KB-01')
GROUP  BY o.order_id, c.name;

--  order_id |    name    | order_total
-- ----------+------------+-------------
--      1001 | Asha Patel |       85.00

The document query reads one collection, because the customer name, the line SKUs and the total are already in the document. Matching on "lines.sku" looks inside the embedded array:

// MongoDB (mongosh)
db.orders.find(
  { status: "shipped", "lines.sku": "KB-01" },
  { "customer.name": 1, total: 1 }
)

// returns: { _id: 1001, customer: { name: "Asha Patel" }, total: 85 }

// Index to support it (a multikey index over the array):
db.orders.createIndex({ "lines.sku": 1 })

The document version is simpler for this question because the model was built for it. Ask a question the model was not built for, such as "units sold per product category last month", and the relational schema answers with another join and a GROUP BY, while the document model needs an aggregation pipeline that unwinds every order's lines, or a category field copied into each line. That asymmetry is the heart of the decision. For more on joins, see the SQL joins guide.

Embedding or referencing

Document modelling is mostly the choice, for each relationship, between embedding and referencing. MongoDB's manual recommends embedding when entities have a "has-a" or "contains" relationship, when the application queries the pieces together, and when the data is updated or archived together. It recommends references when the child side has high cardinality, when embedded data would grow without bounds, when the combined size uses too much memory or bandwidth, when duplication is too complicated to manage, when parts are written at different times in a write-heavy workload, and when the child can exist without the parent.

Hard limits back this up. MongoDB caps a BSON document at 16 MB and lists unbounded arrays, bloated documents and excessive $lookup operations among its schema design anti-patterns. Firestore caps a document at 1 MiB and offers subcollections, so child data can live under a document without being stored inside it. In the example, embedding order lines is safe because an order has a bounded number of lines; embedding every order inside the customer document would not be, because a customer's orders grow without limit.

In the relational model there is no such choice to make per relationship: one-to-many is a foreign key, many-to-many is a junction table, and the query decides what to combine. The design discipline is normalisation, which removes duplication so that each fact can only be updated in one place.

Normalisation, duplication and updates

Duplication is where the two models differ most in day-to-day work. Suppose the customer changes their name. Relationally, one row changes, and every query sees the new value:

-- PostgreSQL
UPDATE customers SET name = 'Asha Patel-Shah' WHERE customer_id = 1;

In the document model you first decide what the copy in each order means. If it is a record of the name at the time of the order, it should not change. If it is meant to be current, every copy must be updated:

// MongoDB (mongosh): update the customers collection, then the copies
db.customers.updateOne({ _id: 1 }, { $set: { name: "Asha Patel-Shah" } })
db.orders.updateMany({ "customer.customer_id": 1 },
                     { $set: { "customer.name": "Asha Patel-Shah" } })

Those two writes touch different documents, so they are only atomic together inside a multi-document transaction. MongoDB's guidance is to duplicate data that rarely changes and to use references for data that is updated often, because frequently updated duplicates create heavy write workloads. In our assessment, the practical rule is: copy fields that are read far more often than they change, or that should be frozen in time (prices, addresses on an invoice); reference everything else.

Schema evolution

In a relational database all rows in a table share one structure, so a change is a migration applied to the whole table. Many changes are cheap: the PostgreSQL documentation states that adding a column with a non-volatile default stores the default in the table's metadata, so no table rewrite is needed even on large tables, while a volatile default such as clock_timestamp() forces a rewrite. Migration tools keep these changes versioned; see Flyway vs Liquibase.

-- PostgreSQL: metadata-only change, no table rewrite
ALTER TABLE orders ADD COLUMN channel varchar(20) NOT NULL DEFAULT 'web';

In a document database new documents can simply take the new shape, and old documents keep the old one until rewritten. MongoDB documents a schema versioning pattern for this: each document carries a schemaVersion field, and the application reads and updates both shapes, migrating old documents gradually if at all. Optional $jsonSchema validation can enforce the new shape for new writes. The flexibility is real, but the cost moves into application code, which must handle every shape that still exists in the collection.

Mixing the two models

The models are no longer tied to separate products. Relational engines store documents inside rows: PostgreSQL's jsonb, MySQL's JSON and SQL Server 2025's native json type let you keep customers and products normalised while storing a variable attribute set as JSON. In the other direction, MongoDB's $lookup joins collections and references model shared entities, and Firestore subcollections give a document database a hierarchy. A common pattern is a normalised core for shared, frequently changing entities, with document-shaped data where records vary or are read whole. See MongoDB vs PostgreSQL for how the two flagship products handle JSON, joins and validation.

Pricing and licensing

The data model itself has no price, but it changes what you pay for. Duplicated fields in a document model use more storage and make some updates write many documents. On services billed per operation, the model also drives the bill: Cloud Firestore, for example, charges for each document and index entry read to satisfy a query, so a model that answers a screen with one document read can cost less than one that needs several, while one that rewrites many copies on each change can cost more. Relational databases on instance-based pricing do not bill per read, so the trade-off there is mainly server capacity. Product prices are covered on the individual comparison pages.

Pricing checked on the vendors' official pages on 7 October 2026. Prices change; confirm before buying.

Where each one leads

Limitations

When to choose each

When neither is right

• Mostly simple lookups by key with no structure to model: a key-value store may fit better; see Redis vs PostgreSQL and DynamoDB vs PostgreSQL.
• Queries that follow relationships many hops deep, such as friends of friends: a graph model is designed for this; see SQL vs NoSQL.
• Analytics over large history: neither operational model is ideal; dimensional (star schema) modelling in a warehouse is; see Data Warehouse vs Database.

Final recommendation

Frequently asked questions

Sources

• MongoDB manual: Data modeling
• MongoDB manual: Embedded data versus references
• MongoDB manual: Schema design patterns
• MongoDB manual: Schema versioning pattern
• MongoDB manual: Schema design anti-patterns
• MongoDB manual: Limits and thresholds
• MongoDB manual: Schema validation
• Cloud Firestore: Data model
• Cloud Firestore: Usage and limits
• PostgreSQL documentation: ALTER TABLE
• PostgreSQL documentation: Constraints
• PostgreSQL documentation: JSON types

Checked October 2026.

How we research comparisons: our editorial method.