Data models &
query languages
The shape you give your data decides what’s easy, what’s awkward, and how you think about the problem.
→ next · ← back · N notes · F full screen
Software is data models stacked on data models.
Each layer hides the complexity below it behind a clean model.
Relational vs document
A fifty-year-old debate, rerun with new names.
Every challenger got hype. SQL stayed.
Lab: one profile, two shapes
| id | name | region_id |
|---|---|---|
| 42 | Sara A. | ir:21 |
| user_id | title | org |
|---|---|---|
| 42 | Backend eng. | Acme Pay |
| 42 | Intern | DataCo |
| user_id | school | from |
|---|---|---|
| 42 | Sharif Univ. | 2014 |
| user_id | type | value |
|---|---|---|
| 42 | blog | sara.dev |
{ "user_id": 42, "name": "Sara A.", "region_id": "ir:21",
"positions": [ { "title": "Backend eng.", "org": "Acme Pay" },
{ "title": "Intern", "org": "DataCo" } ],
"education": [ { "school": "Sharif Univ.", "from": 2014 } ],
"contacts": { "blog": "sara.dev" } }Store "ir:21", not "Greater Tehran".
Anything meaningful to humans may need to change someday. An ID never does.
Removing duplicated human-meaningful data is the whole idea of normalisation.
Data grows more connected with every feature.
Companies become entities with pages and logos. Users recommend each other. Suddenly the tree has many-to-many links.
No joins in the database? Then your application code does them.
We’ve had this argument before.
Hierarchical · IMS, 1968
A tree of nested records, like JSON. Great for one-to-many. No joins: duplicate data or chase references by hand.
Network · CODASYL
Records with many parents, linked by pointers. Queries walk access paths by hand. Change the paths, rewrite the code.
Relational · SQL
Everything laid out in the open. The query optimizer picks the access path. New index? Queries use it automatically.
Build the optimizer once, and every application benefits. The general solution wins in the long run.
So which one leads to simpler code?
Document wins when…
- data is a tree of one-to-many
- you load the whole tree at once
- records are heterogeneous
- you want schema flexibility
Shredding a tree into many tables makes schemas and code clumsy.
Relational wins when…
- you need joins
- many-to-one references are common
- many-to-many relationships appear
- all records share a structure
Denormalising or app-side joins push complexity into your code.
“Schemaless” is a myth. It’s schema-on-read.
Your reading code still assumes a structure. The only question is who enforces it: the database at write time, or your code at read time. Like static vs dynamic typing.
if (user.fullName != null && user.firstName == null) {
// written before the split
user.firstName = user.fullName.split(" ")[0];
}ALTER TABLE users ADD COLUMN first_name text;
UPDATE users
SET first_name = split_part(full_name, ' ', 1);Lab: which model fits?
Locality: a win only if you read the whole thing.
A document is stored as one blob. Reading one field still loads it all; growing it usually means rewriting it all.
Keep documents small.
The two models are converging.
A hybrid of relational and document is a good route for databases to take.
Query languages
Say what you want, not how to get it.
Imperative says how. Declarative says what.
List<Payment> failed = new ArrayList<>();
for (Payment p : payments) {
if (p.status() == FAILED) {
failed.add(p);
}
}SELECT * FROM payments
WHERE status = 'FAILED';Lab: CSS is declarative too
li.selected > p { background: yellow }Click “Run JS once”, then select a different page.
MapReduce: somewhere in between.
Refunded amount per month, three ways:
Moral: NoSQL systems tend to reinvent SQL, in disguise.
Graph-like models
For when anything can relate to anything.
Vertices and edges.
Vertices don’t have to be one kind. Facebook keeps people, places, events, check-ins, and comments in one graph.
CREATE TABLE vertices (id bigint PRIMARY KEY, props jsonb);
CREATE TABLE edges (
id bigint PRIMARY KEY,
tail bigint REFERENCES vertices, -- from
head bigint REFERENCES vertices, -- to
label text, props jsonb);
CREATE INDEX ON edges (tail); CREATE INDEX ON edges (head);- Any vertex can link to any other
- Traverse forward and backward (two indexes)
- Labels keep many relationship kinds in one clean model
- Easy to extend as features grow
Lab: who was born in X and lives in Y?
Same question, four languages.
When one model needs 10× more code, you picked the wrong model for that question.
Triple-stores: everything is a sentence.
_:sara :name "Sara" ._:sara :livesIn _:berlin .Same idea as property graphs, different words. RDF adds URIs so your data can merge with everyone else’s.
Isn’t this just CODASYL again? No.
The whole chapter in one picture.
Each model can emulate the others, awkwardly. That’s why we have different tools, not one size fits all.
Now, our system.
Impedance mismatch
Where does JPA leak through? N+1 queries, lazy-loading surprises, awkward mappings?
IDs or copies?
Which settlement fields reference merchants, currencies, banks by ID, and which copy values on purpose as a snapshot of that moment?
JSON we can’t query
Do we keep JSON in text columns? Should any become jsonb with an index?
Hidden graphs
Merchant groups, account hierarchies, fraud links: are we walking them in Java loops instead of WITH RECURSIVE?
How databases store your data
From a two-line bash database to LSM-trees and B-trees: what really happens below layer 3. Chapter 3, part 1.