Choosing a relational or a document database

A relational database keeps data in tables, and a document database keeps each record as one document. The right choice depends on which questions your app asks.

Your schedule and your instructor's class list for MGT 45 both come from the online enlistment system. One page is organized by student and the other by course. The database behind both holds the same student records.

Your scheduleOrganized by student
Class listOrganized by course
Student recordsOne database
Think first. Every page reads the same records. Which page would you speed up first, the schedule or the class list, and what would you store differently? Write down one idea.
Students tend to name only the questions that suit the layout they already know. Ask for a question that would be hard in each layout. The next step has three such questions.

The same enlistments are shown in two shapes. Pick a shape, then pick a question and read what it costs.

The same data, two shapes
Ask three questions
More detail

Where to read more

A join takes one query when written well. Without that, a page of 50 rows takes 51 queries, as shown in The N+1 query problem. The missed-rows problem in the document shape is the one from Normalization, with no single table to check your work against.

A schema describes the fields of a record. In a relational database, the database enforces the schema and rejects data that does not fit. In a document database, your own code enforces it, so a misspelled field name creates a new field and raises no error.

More detail

The four families, and what "schema" means in each

The common split between SQL and NoSQL databases is a split in where the schema is enforced. Both families have a schema. In a relational database, the database enforces it. In the others, your code does.

Structured: the database enforces the schema
Relational

Tables with fixed columns and declared types. The schema is written down once, and the database rejects any data that does not fit it. Joins let you ask questions you did not plan for.

Postgres · MySQL · SQL Server · Supabase
Unstructured: your code enforces the schema
Document

Each record is a self-describing document. Two documents in the same collection can have different fields, so a typo creates a new field and no error is raised. The schema still exists, and it lives in your application code.

MongoDB · Firebase · DynamoDB
Unstructured: almost no schema
Key-value

A key, a value and nothing else. It is very fast and has no structure for asking questions. It is used more often as a cache in front of another database than as the main store of the data.

Redis · Memcached
Unstructured: built for connections
Graph

Stores relationships as records in their own right and does not use foreign keys. Use it when the main question is how one thing is connected to another across several steps, such as friends of friends.

Neo4j · Neptune

Document databases still need a data model

The entities, attributes and relationships from Entities and attributes and Relationships still apply. A document database does not remove them. It moves the job of enforcing them from the database to your code, where every client has to remember the rules and nobody can see them in one place. This is sometimes a good trade. You still have to design the data.

Start relational. At your data volumes nothing will be slow, and a relational design is easier to fix when you get the schema wrong, as you will at first. Asking a question you had not planned for, without rewriting the storage, is worth more in a semester project than any speed difference.

You can now choose between a relational and a document database.

More detail

What each shape does well

Rows suit questions you did not plan for

Any column can be filtered, joined or grouped later. You do not need to know the questions when you design the schema, and early in a project you do not.

→ the cost is a join, measured in The N+1 query problem
Documents suit one known question

The whole page comes back in one read, with no join and no assembly. This works well when the access pattern is fixed and known. It becomes painful the first time somebody asks a question the documents were not organized for.

→ the copied titles bring back the problem in Normalization
Rules about correctness

If the database itself refuses to give the last slot of a section twice, two students cannot both get it. Enforcing that rule in application code across several documents allows two requests to pass the check at the same time.

→ the problem itself is shown in Transactions
Many systems use both

A common setup uses Postgres for data that must be correct and a faster store for data that must load quickly. This is a normal design.

→ it also means running two systems, as covered later in Monolith or services