Relational and document models
Choose representations and storage boundaries from access patterns, ownership and retention requirements.
THE IDEA
Relational and document models
A relational schema keeps each fact in one place and states relationships and constraints explicitly: renaming a product touches one row, but showing an order joins several tables. A document model packages related data together: one read shows the order, but a product name copied into 1,000 orders must be rewritten 1,000 times. Atomicity and query power depend on the system, and the documents still need an agreed shape.
| relational (normalised) | document (embedded copy) | |
|---|---|---|
| where the product name lives | one row in products | copied into every order document |
| rename product p1 | 1 row updated | 1,000 documents updated |
| show one order with product names | 3 queries (order, items, products) | 1 read |
| constraints | declared in the schema (keys, types, references) | still need a contract; enforced by the application unless the store offers it |
Relational schemas make relationships and constraints explicit; documents package related data for cheap reads. Atomicity and query behaviour of a document store depend on the system chosen, and readers still rely on agreed fields and types.
Predict first. Does a document database remove the need to define a schema contract?
Chapter context
A data model describes the facts a system owns and the relationships it must preserve. Storage choices should follow how those facts are queried, updated and retained, rather than a blanket preference for one database category.
TRY IT YOURSELF
Part 1. Do it hands-on
Count the cost of a rename in both models by hand, then build both representations.
| relational (normalised) | document (embedded copy) | |
|---|---|---|
| where the product name lives | one row in products | copied into every order document |
| rename product p1 | 1 row updated | 1,000 documents updated |
| show one order with product names | 3 queries (order, items, products) | 1 read |
| constraints | declared in the schema (keys, types, references) | still need a contract; enforced by the application unless the store offers it |
Relational schemas make relationships and constraints explicit; documents package related data for cheap reads. Atomicity and query behaviour of a document store depend on the system chosen, and readers still rely on agreed fields and types.
- To do: 1000 orders reference product p1 by id in a relational schema. Renaming p1 requires updating how many rows?
- To do: The same 1000 orders each embed a copy of the product name in their own documents. Documents to update for the rename?
- To do: Showing one order with its items and product names: relational = 3 queries (order, items, products); embedded document = ? query(ies)
- To do: Does a document database remove the need to define a schema contract? Type yes or no
Part 2. Do it in code
- The relational form: products = {pid: {"name"}}, orders = {oid: {"customer"}}, items = list of {"order", "pid", "qty"}.
- order_view_relational(oid, orders, items, products): returns (view, queries). The view is {"customer", "items": [{"name", "qty"}]}; queries is 3 (the order, its items, the products).
- to_document(oid, orders, items, products): the embedded form {"customer", "items": [{"pid", "name", "qty"}]} (product name copied into the document).
- rename_relational(products, pid, new_name): update the product and return the number of rows changed (1, or ValueError for an unknown product).
- rename_documents(docs, pid, new_name): update every embedded copy and return the number of documents changed.
- validate_document(doc, required): the list of required top-level fields missing from the document (sorted). A schema contract still exists in a document store.
Stuck, or something not working? You can come back to it any time.
CHECK YOUR UNDERSTANDING
Does a document database remove the need to define a schema contract?
Chapter reference: math, worked example & code
This section belongs to the whole chapter, so it is the same on every lesson in it and may cover a different lesson's topic. The code for the experiment you just ran is inside the experiment itself.
Math and notation
A simple retained-payload estimate is
where w is writes/day, s payload bytes/write, d retained days and r full-copy count. Add indexes, metadata, compaction headroom and backups separately; replication schemes may store more than exactly r payload copies.
Worked example
Two million records per day at 500 bytes each produce one decimal GB/day of raw payload. Thirty days retained across three full copies require 2,000,000×500×30×3=90,000,000,000 bytes, or 90 decimal GB before other storage. Compressibility and index size are separate measured factors.
Reference code
The following example uses the Python standard library and does not call a remote service.
daily,size,days,copies=2_000_000,500,30,3
assert daily*size == 1_000_000_000
retained=daily*size*days*copies
assert retained == 90_000_000_000
assert retained/1e9 == 90
Python examples are provided for study and local execution; this page does not execute Python.
Common mistakes
NoSQL is not a single consistency or query model. Denormalization creates a maintenance responsibility. Payload estimates must not silently stand in for provisioned disk requirements.