PYTHON / DATABASES WITH PYTHON
Choosing relational or document storage
Decide between normalized tables and embedded documents by mapping your read and write units, then model the same data both ways in Python.
What you will learn
- Model one order two ways: normalized tables versus a single embedded JSON document
- Use SQL GROUP BY for cross-record aggregates instead of looping over documents
- Estimate update fan-out before embedding data that many records reference
- Keep irregular fields in a JSON column when the rest of the schema is stable
Understanding Choosing relational or document storage
The real question is not 'SQL or NoSQL' but 'what is the unit I read and write?'. A relational design splits an order into an orders row plus one items row per line, and reassembles it with a join at read time. A document design stores the order as one nested value, so a fetch by id returns the whole aggregate with no join, but the line items no longer exist as independently queryable rows unless the engine can reach inside the document.
Relational storage pays off when the same fact is referenced from many places and when you slice the data along axes you did not plan for: revenue per SKU, orders per month, customers with no orders. Those are joins and GROUP BY, and the engine does them next to the data with indexes and constraints it enforces itself. Document storage pays off when one document is genuinely the read and write boundary, when fields legitimately differ per record, and when you almost always look things up by a single key.
Every choice buys one cost and sells another. Embedding buys single-fetch reads and pays with duplication: renaming an embedded product touches every document that copied it, and there is no foreign key to stop a dangling reference. Normalizing buys cheap single-place updates and integrity, and pays with joins and up-front schema changes. Decide per entity rather than per project, and remember the middle ground: SQLite and PostgreSQL can store and index JSON columns, and MongoDB can store references instead of embedded copies.
import json
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT);
CREATE TABLE items (order_id INTEGER, sku TEXT, qty INTEGER, price REAL);
CREATE TABLE order_docs (id INTEGER PRIMARY KEY, body TEXT);
""")
orders = [
{"id": 1, "customer": "ana",
"items": [{"sku": "pen", "qty": 2, "price": 1.5},
{"sku": "pad", "qty": 1, "price": 4.0}]},
{"id": 2, "customer": "bo",
"items": [{"sku": "pen", "qty": 5, "price": 1.5}]},
]
for o in orders:
db.execute("INSERT INTO orders VALUES (?, ?)", (o["id"], o["customer"]))
db.executemany("INSERT INTO items VALUES (?, ?, ?, ?)",
[(o["id"], i["sku"], i["qty"], i["price"]) for i in o["items"]])
db.execute("INSERT INTO order_docs VALUES (?, ?)", (o["id"], json.dumps(o)))
rows = db.execute("""
SELECT o.customer, i.sku, i.qty FROM orders o
JOIN items i ON i.order_id = o.id
WHERE o.id = 1 ORDER BY i.rowid
""").fetchall()
print("read order 1 relationally:", rows)
doc = json.loads(db.execute("SELECT body FROM order_docs WHERE id = 1").fetchone()[0])
print("read order 1 as document:", doc["customer"], [i["sku"] for i in doc["items"]])
print("revenue per sku in SQL:",
db.execute("SELECT sku, SUM(qty * price) FROM items"
" GROUP BY sku ORDER BY sku").fetchall())
totals = {}
for (raw,) in db.execute("SELECT body FROM order_docs"):
for item in json.loads(raw)["items"]:
totals[item["sku"]] = totals.get(item["sku"], 0) + item["qty"] * item["price"]
print("revenue per sku in Python:", sorted(totals.items()))Choose relational when data is queried across many axes and shared by many records; choose documents when the natural unit of read and write is one self-contained aggregate.
Worked examples
Irregular fields: rejected column vs extra key
Shows why records whose fields differ per category are cheap in a document and expensive in a fixed table.
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE products (sku TEXT PRIMARY KEY, name TEXT, price REAL)")
db.execute("INSERT INTO products VALUES ('pen', 'Blue pen', 1.5)")
try:
db.execute("INSERT INTO products (sku, name, price, voltage)"
" VALUES ('lamp', 'Desk lamp', 19.0, 12)")
except sqlite3.OperationalError as e:
print("relational rejected:", e)
docs = [
{"sku": "pen", "name": "Blue pen", "price": 1.5},
{"sku": "lamp", "name": "Desk lamp", "price": 19.0, "voltage": 12},
]
print("document keys:", [sorted(d) for d in docs])
print("reader must cope:", docs[0].get("voltage", "n/a"))Example explained
Line 1The INSERT fails because a relational table's column set is fixed; adding voltage needs an ALTER TABLE migration.
Line 2The two dicts happily carry different keys, which is the flexibility document storage is bought for.
Line 3The final .get('voltage', 'n/a') is the hidden price: the shape check moved out of the database and into every reader.
Line 4Nothing here validates that voltage is a number, so bad values are only discovered when Python reads them.
Update fan-out when you embed a shared name
Compares how many writes a product rename costs under a reference versus an embedded copy.
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE products (sku TEXT PRIMARY KEY, name TEXT)")
db.executemany("INSERT INTO products VALUES (?, ?)",
[("pen", "Blue pen"), ("pad", "Notepad")])
cur = db.execute("UPDATE products SET name = 'Blue gel pen' WHERE sku = 'pen'")
print("relational rows updated:", cur.rowcount)
orders = [
{"id": 1, "items": [{"sku": "pen", "name": "Blue pen"}]},
{"id": 2, "items": [{"sku": "pen", "name": "Blue pen"},
{"sku": "pad", "name": "Notepad"}]},
{"id": 3, "items": [{"sku": "pad", "name": "Notepad"}]},
]
rewritten = 0
for o in orders:
hits = [i for i in o["items"] if i["sku"] == "pen"]
if hits:
for i in hits:
i["name"] = "Blue gel pen"
rewritten += 1
print("documents rewritten:", rewritten)
print("order 2 now:", orders[1]["items"][0]["name"])Example explained
Line 1cur.rowcount is 1 because the name lives in exactly one place and orders point at it by sku.
Line 2The document version rewrites one order per embedded copy, so the cost grows with order volume, not with catalogue size.
Line 3If the loop crashed after order 1, half the orders would show the old name; there is no single statement covering all documents.
Line 4The fan-out count, not elegance, is the number to check before deciding to embed.
Important notes
Copying a price into an order line is not the duplication problem: the order is a historical record and must not change when the catalogue price does. Duplication only hurts when the copy is meant to stay in sync.
'Relational' does not mean flat. SQLite's json functions and PostgreSQL's jsonb let you query and index inside a stored document, which often removes the reason to change database entirely.
Common mistakes
Picking a document store to avoid designing a schema; the schema does not vanish, it reappears as .get() calls and shape checks scattered through every reader, and old records keep their old shape forever.
Embedding a shared entity such as a product name or stock level in every order, then discovering a rename means rewriting thousands of documents with no single statement to make it atomic.
Loading whole documents into Python and summing in a loop for reports; you transfer the entire collection over the network and pay memory for data the database could have aggregated with GROUP BY or an aggregation pipeline.
Try it yourself
Change, predict, then run
Build an in-memory sqlite3 database that stores the same blog post twice: as posts plus comments tables, and as one row with a JSON body. Then produce 'number of comments per author across all posts' from each shape and note how many lines of Python each needs.
Open the Python workspaceCheck your understanding
A catalogue has 40 product fields, but only 6 are shared across categories while the rest depend on the category, and every read is 'give me one product page by id'. What is the strongest argument for document storage here?
- Each read is a single self-contained fetch by key, and category-specific fields cost no nullable columns or extra join tables
- Document databases read a single record faster than relational databases do
- Relational databases cannot store nested lists such as an image gallery
- Document storage guarantees the shape of each record, so readers need fewer checks
Show answer
The access pattern matches the storage unit and the irregular fields need no schema gymnastics, which is a modelling win. Option 2 is the tempting one but wrong: a primary-key lookup in a relational table is equally cheap, so raw speed is not the argument. Option 4 is inverted, since documents shift shape enforcement into your Python code.