مدل‌های داده و زبان‌های کوئریData models and query languages

Read the four bars top to bottom: L1 App objects (people and orders in code), L2 Storage model (JSON, tables or graph — this chapter’s subject), L3 Storage engine (bytes on disk and memory — Chapter 4), and L4 Hardware (current, light, magnetism). The three right-hand tags (abstracts / encodes / embodies) say what each layer does to the one below: hide it, encode it, embody it.

Read the two cards side by side. Left (Shredding): three small tables — users, positions, education — tied by foreign keys; reading means a JOIN every time, writing means one row at a time. Right (Embedding): one JSON document holding everything; reading means a single lookup, writing means rewrites and copies. The bottom line is the selection rule: embed a one-to-few tree, reference a many-to-many web.

This ER diagram shows the previous diagram’s resume in the relational model. Start from the mid-left box: “users” (“PK user_id, name, FK region_id”). Three “1..N” arrows leave it: to “positions” above, “education” in the middle and “contact_info” below. Each user owns several rows in each — the same one-to-many tree that JSON would embed.

Now follow the dashed “N..1” arrow from users to “regions” (tagged “normalized”): many people share one region, but the region’s name is stored exactly once. Instead of repeated text we keep an ID, paying one extra lookup or join at display time.

On the right, “positions” connects with “N..1” to “organizations” (“entity”, “PK org_id, name, logo_url”). That is the normalization call: if an organization’s logo changes, only one row changes. And look closer: users and organizations effectively hold a many-to-many relationship, built by that same positions table: each positions row ties one user_id to one org_id.

Let’s read this diagram together, step by step. First your eye lands on the top half: the “Profile Page (SQL)” box (“fetch full resume”). Three arrows leave its right edge.

The first, bold arrow, labelled “query + join”, reaches the users table; the next two share one label, “join on user_id”, and reach the positions and education tables. Building one resume page means joining three tables.

Now follow the dashed “resolve ID” arrow: from the users table to the regions table, tagged normalized. That is normalization: the human-meaningless region_id becomes a readable name here.

Move to the bottom half. Here the “Profile Page (JSON)” box has a single bold arrow: “single read”, straight into the “Profile Document”; a document subtitled “positions + education inside” and tagged “one read”. This half’s only dashed arrow, “by region_id”, goes to the regions collection.

The lesson is in this contrast: above, several queries and joins to rebuild one tree; below, a single read — but the price below is data duplication and the copy-update problem.

The figure has three panels, each titled above. In the left panel — “A · Relational: tables + foreign keys” — you see two class-like boxes. The top box is “person” with three attributes: “person_id PK”, “first_name” and “last_name”. The bottom box is “job” with “job_id PK”, “person_id FK”, “title” and “start_date”. Between them a vertical line with a composition diamond carries two cardinality labels: “1” by person and “0..*” by job — each person has zero or more jobs, each job belongs to one person; the “person_id” label on the line is the foreign key.

The middle panel — “B · Document: one self-contained tree” — holds one big box: “resume (one JSON document)” with “_id”, “name” and nested arrays: “jobs: [ { title, start_date }, … ]”, “education: [ ... ]” and “skills: [ ... ]”. Below sits the small “job (embedded, not a table)” box with “title” and “start_date”, joined to the document by a composition diamond labelled “1” and “0..* embedded”. Here job is no separate table; it lives inside the same document, so reading the resume is a single read.

The right panel — “C · Graph: nodes and relationships are first-class” — has three node boxes: “(:Person)” with “name” and “born”, “(:Company)” with “name” and “industry”, and “(:City)” with “name” and “country”. Three green arrows run between them: Person to Company labelled “[:WORKS_AT {since}]”, Person to City “[:LIVES_IN]”, Company to City “[:LOCATED_IN]”. Note the first label carries an attribute: “{since}” — an edge can hold data itself, something a plain foreign key has no room for.

Now read the three short notes under each panel. Panel A: “each row is one thing; references must stay valid” and “showing the resume means JOIN at read time”. Panel B: “all of one user’s fields sit together — no join” but “denormalization means duplicated data in several places”. Panel C: “relationships carry their own data” and “query cost depends on traversal, not schema”. The bottom line of the whole figure concludes: same information, three shapes — the model decides which access is cheap and which needs transactions or duplicated data.

The lesson: “object-relational mismatch” is exactly this gap between panel A’s boxes and the in-code object. The document model closes it by fitting the whole tree in one document — but pays at duplicated-data update time. And the graph model wins when your question is fundamentally “who connects to what”.

Read this figure like a simple map. On the left, three boxes form a geographic chain: “Idaho” at the bottom, “United States” above it, “North America” higher still. Two vertical arrows labelled WITHIN stitch the chain: Idaho is “within” the US, the US “within” North America.

Now find the “Lucy” box, subtitled Person. Two bold arrows leave it: one labelled BORN_IN to Idaho, another labelled LIVES_IN to “London”, sitting mid-figure.

On the right the same pattern repeats: “Saint-Lo” WITHIN “France”, France WITHIN “Europe”. Below them sits “Alain”, with both BORN_IN to Saint-Lo and LIVES_IN to London. And a horizontal MARRIED_TO arrow joins the two people.

Now run the book’s question on this figure: “people born in the United States who now live in Europe”. Take a person node, follow BORN_IN to their birthplace, then climb the WITHIN*0.. chain (zero or more “within” hops) up to the United States box; that same person must reach, via LIVES_IN, a place that reaches Europe via WITHIN*0... Lucy fills exactly this pattern.

And the golden interview point: the same four-line pattern costs ~31 lines of SQL with WITH RECURSIVE — because the number of joins in a graph is unknown upfront.

Unlike previous diagrams, this one shows no “flow”; it shows structure — just like a UML class diagram. On the left sit two “classes”: “Vertex” with fields “id, label, properties” tagged “entity”, and below it “Edge” with “id, tail, head, label, props” tagged “relationship”. The dashed “tail + head” arrow from Edge to Vertex says each edge points at two vertices.

Now see how these same two “classes” map onto a relational database on the right: the bold “maps to” arrow runs from Vertex to the “vertices table” (“PK vertex_id, label, jsonb”) and from Edge to the “edges table” (“PK edge_id, FK tail, FK head”). The dashed “FK references” arrow between the tables is the foreign keys.

And the last box, “Indexes”, tagged “both directions”: an index on tail_vertex for outgoing edges and on head_vertex for incoming ones. Without these two indexes every graph traversal must scan the whole edges table; with them, walking the graph stays fast.

This figure flows left to right, its top columns naming the stages: Commands → Validate → Event Log → Materialized Views → Reads.

In the middle row, the “Client App” box (“book, cancel”) starts it off. The bold “command” arrow reaches the “Command Handler”; a box whose subtitle says it all: “validate, then emit” — validate first, then emit the event.

The next bold arrow, “append event”, reaches the system’s heart: the “Event Log” box, tagged “source of truth” and subtitled “append-only, immutable”. Nothing ever changes; things only get written.

Now three dashed arrows leave this box’s right edge, each a projection: “booking view” climbs to “Booking View”, “chart data” runs right to “Dashboard Charts”, and “badge files” drops to “Badge Files”. All three carry the projection tag; all are rebuildable from the log.

In the last column, each view connects to its consumer: “query view” arrows to “Support UI” and “Organizer View”, “render badges” to “Badge Printer”. Note one thing well: no arrow ever points right-to-left; change happens only by appending a fresh event — and a mistaken event is fixed by writing a fresh corrective event.

This state machine shows one bookable seat’s life under event sourcing. Start from the top lane, “Booking states”: the “Open” start box (“registration starts”) moves via “capacity set” to “Available”. Then the bold SEAT_BOOKED transition to “Reserved” and bold PAYMENT_RECEIVED to “Confirmed”. Note: transition names are past-tense events, not commands.

From “Reserved” a guard arrow labelled SEAT_CANCELED drops to the lower lane: the “Canceled” box in “Terminal states”. Then the dashed “seat released” arrow returns it to “Available” — the seat frees up, but its history is never erased.

The most instructive corner is the right one. If a mistake surfaces from “Confirmed”, the dashed “mistake found” arrow goes to the “Correction Event” box in the “Corrections” lane; its subtitle says everything: “new event, never edit”. And a “compensating event” carries the state back to “Available”. Data is never edited anywhere; only fresh events append — and that is auditability and reproducibility.

Read this figure as a decision path. In the top lane, “Classify”, two boxes sit side by side: “Data Shape” (“tree, web, or events”) and “Access Pattern” (“tree, hops, scans”). The bold arrow between them is labelled “classify”: data shape first, access pattern second.

From “Access Pattern” four arrows fan out to the options. In the “Flexible Shapes” lane sit two: “Document”, tagged “one-to-few” with the “tree, one-to-few” arrow heading to it, and “Graph” tagged “many-to-many” with the short “hops” arrow.

In the lower lane, “Structured History”, sit the other two: “Relational / Star”, reached by the bold “joins, analytics” arrow — its boldness marks the default path of typical decisions — and “Event Log + CQRS”, reached by the dashed “writes, many reads” arrow.

The path’s lesson: once you fill in the left-hand boxes, the right-hand choice is nearly automatic.

Read the central node (Data models and query languages) and follow the six edges: normalize to relational with tables and JOIN, embed to documents with one JSON tree, traverse to graphs with nodes and edges, derive to the event log as append-only truth, optimize to declarativeness (say WHAT, hide the how), and decide to the selection rule: relationship shape first, access pattern second.