Relational vs Document Data Models

How tables with joins and self-contained documents each fit the shape of your data, and how to choose between them for a service.

Overview

A data model is the shape you give your data when you store it. The two shapes most services choose between are tables (the relational model, used by PostgreSQL and MySQL) and documents (JSON-like trees, used by MongoDB and similar stores). Neither is better in general. Each one makes some reads and writes easy and others awkward, and the right choice depends on how the pieces of your data connect.

Why the choice exists: objects are trees, tables are flat

Application code usually holds data as nested objects: a user with a list of addresses, each with its own fields. A relational database stores the same data as flat rows in several tables, linked by keys. Something has to translate between the two, and that translation is called the impedance mismatch. Object-relational mappers (ORMs) such as Hibernate or Django's ORM do the translation for you, but they cannot hide it completely: you still think about joins, lazy loading and how many queries a page makes.

The relational model: tables, keys and joins

Edgar Codd proposed the relational model in 1970. His paper's first point was that people using a database should not have to know how the data is laid out on disk. Data lives in tables of rows with typed columns. A row points to a row in another table with a foreign key, and a query puts related rows back together with a join. Because the query says what it wants and not how to find it, the database can pick the access path itself, and the layout can change without breaking the application.

Keeping each fact in one place is called normalization. A company name is stored once in a companies table, and every job row refers to it by id. Rename the company and every job sees the new name at once.

Ana — Engineer
Raj — Designer

The document model: one tree per record

A document store keeps each record as one self-contained tree, usually JSON or a binary form of it (MongoDB uses BSON). A user profile with its addresses and jobs nested inside is a single document. This gives locality: everything about one entity sits together, so one read fetches it and one write updates it. MongoDB states that an operation on a single document is atomic, which covers many cases that would otherwise need a transaction.

Locality has limits, though. A MongoDB document can be at most 16 MiB, so a list that grows without bound (every comment on a popular post) does not belong inside one document. And when the application only needs one small field, the store still reads the whole document.

Load one whole profile — Several rows from several tables, joined — One document
Update one profile atomically — A transaction over several rows — A single-document write
Find everyone who worked at Acme — One join on an indexed key — An index on jobs.company, but only if the name is spelled the same everywhere
Rename a company — One row — Every document that copied the name

Where documents struggle: shared entities

A tree works while every piece belongs to exactly one parent. Real data rarely stays that way. A company is shared by many people's job histories (many-to-one), and a person can work at many companies while each company has many people (many-to-many). A document store then has two options, and both cost something.

Duplicate the shared data into each document. Reads stay fast, but a rename must touch every copy, and a missed copy leaves the data inconsistent.
Reference it by id, as a table would. The data stays correct, but putting it back together is a join, done either with the store's join operator (MongoDB's $lookup) or by extra queries in application code.

The more connected the data becomes, the more the application ends up doing the database's join work by hand. When connections themselves are the main thing you query (friends of friends, fraud rings), a graph model fits better still.

The two models have moved toward each other

Today the line between them is blurry. PostgreSQL has a jsonb column type that stores JSON in a parsed binary form and can be indexed with a GIN index, so a relational table can hold document-shaped fields. MongoDB added a left outer join ($lookup, usable with sharded collections since 5.1), multi-document transactions (replica sets since 4.0, sharded clusters since 4.2) and optional JSON Schema validation.

Nested, flexible fields — jsonb columns with GIN indexes — Native
Joins — Native, planned by the optimizer — $lookup in the aggregation pipeline
Multi-record transactions — Native — Since 4.0 (replica sets) and 4.2 (sharded)
Enforced structure — Column types and constraints — Optional $jsonSchema validator

So the real question is not which product, but which shape your main access patterns want.

What you will be able to do

Explain the impedance mismatch and why ORMs only hide part of it.
Say what locality buys a document store and where its limits are.
Show why shared entities push a design toward tables and joins.
Describe how jsonb and $lookup blur the line between the two models.
Pick a model for a service in an interview and state what it costs.

Key concepts

Impedance mismatch: The gap between nested objects in application code and flat rows in tables. ORMs translate between them but cannot hide joins and query counts completely.
Normalization: Storing each fact once and referring to it by key. A change happens in one place, at the cost of joins when reading.
Denormalization: Copying data into the places that read it, so reads need no join. Every copy must be updated when the fact changes.
Locality: Keeping everything about one entity together, so one read or write handles it. The main advantage of a document.
Foreign key: A column whose value is the key of a row in another table. The database can check that the referenced row exists.
Many-to-many relationship: Each item on one side links to many on the other, such as people and companies. Tables model it with a link table; trees model it poorly.
jsonb: The PostgreSQL column type that stores JSON in a parsed binary form. It supports containment queries and GIN indexes.
$lookup: The MongoDB aggregation stage that does a left outer join with another collection in the same database.

When to use it

Choosing a database for a new service in a design interview
Explaining why a design stores some data in tables and some as JSON
Deciding whether to embed child data in a document or reference it by id
Discussing the cost of duplicating (denormalizing) shared data
Answering a broad "SQL or NoSQL?" question with a concrete reason

Interview questions

When would you choose a document database over PostgreSQL?
What is the impedance mismatch, and how do teams deal with it?
A user profile has jobs, and each job has a company. Would you embed or reference the company?
Has the difference between relational and document databases disappeared?