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.
The same enlistments are shown in two shapes. Pick a shape, then pick a question and read what it costs.
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.
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 · SupabaseDocument
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 · DynamoDBKey-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 · MemcachedGraph
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 · NeptuneDocument 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 problemDocuments 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 NormalizationRules 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 TransactionsMany 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