Software Architecture

Appearance

Chapter 3: Data Fundamentals ​

General Track · Foundational · ~35 min read + exercise

Why this chapter exists ​

The last two chapters were about the shape of your code — the trade-offs you make and the structural style you choose. This chapter is about the thing underneath all of it: the data. And there's a reason it comes early rather than late. Of everything in a system, the data tends to outlive the rest. Frameworks get replaced, services get rewritten, the architecture style shifts as the company grows — but the data, and the decisions you made about how to model it, persist for years, often decades. As Tim Berners-Lee put it, data is a precious thing that lasts longer than the systems themselves.

That longevity is exactly why data decisions are so consequential and so hard to walk back. Change a function and you ship a deploy. Change how a million rows are modeled — split a table, change a key, move from one consistency model to another — and you're running a migration that touches live production data, can't easily be undone, and has to work the first time. The cost of a bad code decision is measured in refactoring hours. The cost of a bad data decision is measured in migrations, downtime, and sometimes data you can never fully reconstruct. This asymmetry is the whole reason to think hard about data before you're committed to it.

Here's a concrete version of the asymmetry. Imagine your Laravel ticketing app stored each customer's support history as a single JSON blob on the customer row — easy to write, one query to load everything about a customer. Eighteen months later, the business wants to report on tickets across all customers: how many are open, average resolution time, which expert closed the most. Now you're trying to run aggregate queries against data buried inside JSON blobs scattered across customer rows, and the database can't index or join into them efficiently. The "easy" early modeling decision — blob it all together — bought fast writes and simple reads of one customer, and the bill came due as the near-impossibility of querying across customers. Had the tickets been their own table with proper columns and foreign keys from the start, the report would be a five-line query. The early decision wasn't wrong because blobs are bad; it was wrong because it optimized for an access pattern that turned out not to be the one that mattered.

That's the thread running through this whole chapter: data decisions are bets on how you'll access the data, and the trade-offs all flow from matching your structure to your access patterns. Model for the reads and writes you'll actually do, and the system stays fast and flexible. Model for the wrong ones — or model without thinking about access at all — and you pay in slow queries, awkward migrations, and consistency bugs.

This chapter is the most "computer-sciencey" of the foundational set, and it draws heavily on Martin Kleppmann's Designing Data-Intensive Applications, which is the definitive modern treatment. We're compressing a dense 600-page book into the parts every architect needs as working knowledge. The goal isn't to make you a database engineer — it's to give you enough fluency in data modeling, indexing, consistency, and replication that you can reason about the data side of an architecture decision instead of treating the database as a magic box.

What this chapter covers ​

  • Data modeling — relational, document, and other shapes, and how the choice follows from your access patterns and relationships.
  • Indexing — the single most important performance lever most developers half-understand, framed as the read-speed-vs-write-cost trade-off it actually is.
  • Consistency — what it really means for data to be "consistent," and the spectrum from strong to eventual that we previewed in Chapter 1.
  • Replication — why you copy data across machines, and the trade-offs each copying strategy makes.

By the end you'll do a hands-on exercise modeling a real feature's data, reasoning explicitly about access patterns, and identifying where the consistency and indexing trade-offs land.

Let's start where every system starts: deciding what shape the data takes.

Data modeling: shape follows access ​

Before you pick a database product, you're making a more fundamental choice: what shape does your data take? The two shapes you'll meet most are the relational model — data split into tables of rows and columns, with relationships expressed by keys that link tables — and the document model — data nested into self-contained documents (typically JSON-shaped), where related data lives together inside one document rather than in separate tables.

The relational model is the default for good reasons that have held up for fifty years. It separates each kind of thing into its own table, which means each fact is stored once (normalization), and it lets you combine data flexibly at query time by joining tables on their keys. Here's the ticketing data from the opening, modeled relationally:

Because tickets are their own table with real columns and foreign keys, the cross-customer report that was impossible with blobs becomes trivial: select status, count(*) from tickets group by status. The relational model shines exactly where you need to query data in ways you didn't fully anticipate up front — which, for most business applications, is constantly. New reporting needs, new admin screens, new filters: the relational model absorbs them because the data isn't pre-committed to one access shape.

The document model makes the opposite bet. By nesting related data together — a customer document that contains its tickets, each ticket containing its notes — it optimizes for loading one entity and everything under it in a single read, with no joins. If your dominant access pattern is "fetch this one customer's whole world and render it," a document store is fast and natural, and the data maps cleanly onto the objects your code already uses. The cost is the mirror image of the relational model's strength: querying across documents (all tickets regardless of customer) is awkward or slow, and data duplicated across documents (the expert's name copied into every ticket) can drift out of sync and is painful to update everywhere.

Notice this is the same trade-off the chapter opened with, now named precisely. Normalized relational data optimizes for flexible querying and single-source-of-truth correctness, paying with the need to join (and the work that joins cost at scale). Denormalized document data optimizes for fast whole-entity reads, paying with hard cross-cutting queries and update anomalies. Neither is more advanced — they're bets on different access patterns, exactly the kind of trade-off you've been training to see.

How to actually decide ​

The useful question is never "relational or document?" in the abstract. It's a sequence of questions about how the data will be used:

What are the relationships? Data rich in many-to-many relationships (tickets relate to experts, experts to skills, skills to other things) is the relational model's home turf — joins express those relationships naturally, and a document model forced to represent them ends up duplicating data or faking joins in application code. Data that's genuinely hierarchical and self-contained (a document and its revisions, an order and its line items that are only ever read with the order) fits the document shape.

What are the dominant access patterns? If you mostly read and write whole entities at a time, documents serve that well. If you mostly query, filter, and aggregate across many entities in varying ways, relational serves that well. Be honest about current patterns rather than imagined future ones — the same caution against premature flexibility from Chapter 1 applies here.

How much will the access patterns change? This is the decider when the others are close. If you can't predict how you'll need to query the data — common early in a product's life — the relational model's flexibility is a hedge against your own uncertainty, because it doesn't pre-commit the data to one access shape. Document stores reward you when you know the access pattern and it's stable; they punish you when the pattern shifts and you discover your data is shaped wrong for the new questions.

A practical real-world note: most systems are not purely one or the other. A relational database is a perfectly good default for the core of most business applications, and modern relational databases (Postgres especially) have strong JSON column support, so you can keep most data normalized and use a JSON column for the genuinely document-shaped, schema-flexible corner of the model — the best of both for many cases. The mistake isn't choosing one; it's choosing without thinking about access patterns at all, the way the opening's blob decision did. There are other specialized shapes too — key-value stores for simple fast lookups, graph databases for data that's mostly about relationships between nodes, time-series databases for metrics and events — and the same logic decides them: match the data's shape and the database's strengths to how you'll actually access it.

Once you've chosen a shape and started querying it in earnest, the next thing you'll confront is speed — and the lever that matters most there is indexing.

Indexing: buying read speed with write cost ​

Indexing is the performance lever most developers reach for, and the one most half-understand. People learn "add an index to make queries fast" as a spell rather than a trade-off, which leads to both under-indexing (slow queries nobody diagnosed) and over-indexing (every column indexed "just in case," quietly taxing every write). Understanding what an index actually is fixes both.

Here's the intuition. Imagine a printed book with no index and no table of contents. To find every mention of "circuit breaker," you read all 400 pages cover to cover. That's a database doing a full table scan — checking every row because it has no faster way to find matches. Now imagine the book's index at the back: an alphabetized list of terms, each pointing to the pages where it appears. To find "circuit breaker" you flip to the C's and jump straight to the right pages. A database index is exactly this: a separate, sorted data structure that lets the database jump to matching rows without scanning the whole table.

The speedup is not marginal. On a large table, the difference between an indexed lookup and a full scan is the difference between reading one shelf of a sorted card catalog and reading every book in the library. Queries that take seconds without an index can take milliseconds with one. This is why indexing is the first thing to look at when a query is slow, and why a missing index on a column you filter or join by is among the most common causes of database pain.

So if indexes make reads dramatically faster, why not index everything? Because — and this is the part the "add an index" spell omits — an index is a second copy of the data that the database must keep in sync on every write. The back-of-book index only stays useful if, every time the author adds or changes a page, someone updates the index too. In a database, that someone is the database, and it happens on every insert, update, and delete. Each index you add makes writes a little slower (the index must be updated) and consumes additional storage (it's a copy). Index every column and you've made a table that reads fast and writes like molasses, bloated with index structures.

That's the trade-off in one line: an index buys read speed and pays with write speed and storage. It's the same shape as every trade-off in this course — you're spending one quality attribute (write performance) to buy another (read performance) — and the right number of indexes depends entirely on your read/write balance, which is itself an access-pattern question. A table that's read constantly and written rarely (a product catalog) wants generous indexing on the columns you filter by. A table written constantly and read in bulk later (an event log, an audit trail) wants minimal indexing, because every index is a tax on the thing it does most.

What this means in practice ​

A few working principles fall out of understanding indexes as a trade-off rather than a spell:

Index the columns you filter, join, and sort by — not every column. If your ticketing app constantly queries tickets by status and customer_id, those columns earn indexes. A description text column you never filter by does not. Let the actual queries, not a vague sense of "more is faster," decide.

Watch the write-heavy tables. On a table that takes a high volume of writes, each extra index has a real cost on the operation you do most. Be stingy there, and make each index justify itself against a query that genuinely needs it.

Composite indexes have an order, and the order matters. An index on (customer_id, status) speeds queries filtering by customer_id alone or by both together, but not queries filtering by status alone — the same way a phone book sorted by last-name-then-first-name helps you find "Smith" or "Smith, John" but is useless for finding everyone named "John." Matching composite index order to your query patterns is a common, high-leverage tuning step.

Measure, don't guess. Every serious database can show you its query plan — whether a query used an index or fell back to a full scan (EXPLAIN in Postgres and MySQL). This turns indexing from guesswork into evidence: run the slow query, read the plan, see the scan, add the index, confirm the plan now uses it. The Chapter 1 discipline of "profile before optimizing" applies directly — don't add indexes by intuition, add them where a query plan shows you the scan.

A closing caution that connects back to the framework chapters: ORMs like Eloquent make it easy to generate queries without seeing the SQL they produce, and easy to create the N+1 problem from Chapter 1 — a loop that issues one query per iteration, each possibly doing a full scan. Indexing won't save you from a query pattern that's wrong in shape; it makes individual queries fast, but issuing a thousand of them in a loop is still a thousand round trips. Indexing and query shape are two different levers, and you need both: the right indexes on the right columns, and query patterns that don't multiply needlessly.

Indexing makes a single database fast. But there are limits to what one machine can do — and questions about what happens when the data needs to be correct across many readers and writers at once. That pulls us into the deepest and most misunderstood topic in data: consistency.

Consistency: when does the data have to be right? ​

"Consistency" is one of the most overloaded words in our field, so let's pin it down. In the sense that matters here, consistency is about whether everyone reading the data sees the same, correct value at the same time — and whether the data obeys the rules you expect even when many things are reading and writing it at once, or when it's copied across multiple machines.

Start with the single-database case, where consistency is most achievable and best understood. A relational database gives you transactions — the ability to group several operations so they all succeed or all fail together, with the database guaranteeing certain properties along the way. These properties are summarized by the acronym ACID: atomicity (all-or-nothing — a half-completed transaction never persists), consistency (the data moves from one valid state to another, respecting your constraints — though, unlike the other three, this one is largely the application's responsibility: the database enforces the constraints you declare, but what counts as a "valid state" is something your code defines), isolation (concurrent transactions don't see each other's half-done work), and durability (once committed, it survives crashes). ACID is why a bank transfer in a single database is safe: debiting one account and crediting another happen atomically, so you can never lose money to a crash that lands between the two steps.

This is the comfortable world, and for a system that fits on one database, it's the right default. Lean on transactions. When you're moving money, changing inventory, or doing anything where a half-completed operation would corrupt the truth, wrap it in a transaction and let ACID do its job. A surprising amount of "consistency bug" pain comes from not using transactions where they were available and appropriate.

The spectrum, not the switch ​

The trouble starts when one database isn't enough — when you add a cache, a read replica, or split data across services, all of which we saw arrive in the earlier chapters. The moment the data exists in more than one place, "is the data consistent?" stops being a yes/no and becomes a question of how much inconsistency, for how long, can you tolerate. Consistency is a spectrum, not a switch.

The consistency spectrum, from strong to eventual, with example data types placed along it

At the strong end, every reader always sees the latest write, immediately, as if there were one copy — what a single database gives you. It's the easiest to reason about and the most expensive to maintain across multiple machines, because the copies must coordinate on every read and write, which costs latency and limits how far you can scale. At the eventual end, the copies are allowed to disagree for a while and are guaranteed only to converge eventually, once the writes have propagated. It's far cheaper and scales beautifully, but a reader might see stale data in the meantime, and your application has to be okay with that.

This is the same spectrum from Chapter 1, and the same key insight applies: consistency is not one global setting — different data sits at different points. The decision is made per piece of data, by asking what the cost of staleness actually is. A customer's account balance or a product's price needs strong consistency — showing a stale balance is a correctness and trust failure. A product's view-count, a "last seen" timestamp, or a search index can be eventually consistent — nobody is harmed if the view-count is thirty seconds behind, and demanding strong consistency for it would mean paying a steep coordination cost for a guarantee nobody needed. The architect's job is to place each piece of data on the spectrum honestly, rather than reflexively demanding strong consistency everywhere (needlessly expensive) or shrugging into eventual consistency everywhere (quietly corrupting the data that mattered).

There's a deep result lurking here worth knowing by name: in a distributed system, when a network partition happens — some machines can't talk to others — you're forced to choose between staying available (keep serving, accept that copies may diverge) and staying consistent (refuse to serve rather than risk a wrong answer). You cannot have both during a partition. This is the heart of the CAP theorem — though it's worth being precise about its "C," because this is exactly where the popular version goes wrong: CAP's consistency means specifically linearizability (every read sees the most recent write, as if there were a single copy), not ACID's transactional "C" and not "consistency" in the loose everyday sense. Conflating those three is the oversimplification Designing Data-Intensive Applications was written partly to correct, so don't carry it out of this chapter. The practical takeaway is still simple and durable: distributing data forces consistency trade-offs that a single database lets you avoid. It's one more reason the earlier chapters' advice — don't distribute until you must — runs so deep. Every copy you make is a consistency decision.

Replication: why you make copies anyway ​

Given that copies create consistency headaches, why make them? Because copies buy things you often genuinely need. Replication — keeping copies of the same data on multiple machines — buys three things. It buys read scalability: spread reads across replicas and one machine's read ceiling stops being the system's ceiling. It buys availability: if one copy's machine dies, another can serve, so a single hardware failure doesn't take the data down. And it buys locality: a replica near your users in another region serves them faster than a round trip across the world.

The trade-off is exactly the consistency cost we just covered, and it shows up as the choice of when the copies sync. With synchronous replication, a write isn't acknowledged until the replica has it too — so that replica won't hand back a stale version of what you just wrote, but every write waits for the slowest replica, and if a replica is unreachable, writes can stall. With asynchronous replication, the write is acknowledged immediately and propagates to replicas shortly after — so writes stay fast and a slow replica doesn't block them, but there's a window where a replica is behind, which is precisely the replication lag from Chapter 1's read-replica example, where a user updates a value and immediately reads the stale one from a not-yet-caught-up replica. Sync replication chooses consistency; async chooses performance and availability. Same trade-off, made at the level of how your copies stay in step.

You rarely configure replication by hand as an application developer — your database or cloud provider does the mechanics — but you absolutely make the decisions it implies: whether a given read can tolerate a replica's lag, whether a workflow can read its own just-written data (it can't reliably, off an async replica, without care), and how much staleness each part of your system accepts. Those are consistency decisions wearing replication clothes, and you now have the frame to make them deliberately.

That's the data toolkit: model for your access patterns, index for your read/write balance, place each piece of data on the consistency spectrum, and understand what your copies cost you. Time to put it to work.

Practice ​

The skill here is reasoning about data from access patterns first, and seeing the consistency and indexing trade-offs explicitly rather than defaulting into them. The exercises build toward a small data design you can defend. Budget about two hours.

Exercise 1: Reverse-engineer the access patterns (20 min) ​

Pick a table or collection in a system you work on. Without looking at the code, write down how the data is actually accessed: what queries read it, what writes it, how often each happens, and whether it's read/written as whole entities or queried and aggregated across many. Then check your guesses against the real code or query logs. The gap between what you assumed and what's true is exactly the gap that produces mismodeled data — practice seeing it.

Exercise 2: Model a feature two ways (45 min) ​

Take a feature — yours or invented (say, a commenting system, an activity feed, an audit log). Model its data twice: once relational (tables, keys, the relationships drawn out), once document (nested, self-contained). For each, write two or three sentences on which access patterns it makes fast and which it makes awkward or slow. Then state which you'd choose for the access patterns you expect, and why. The point is to feel that neither model is simply better — each is a bet on how the data gets used.

Exercise 3: Place the data on the consistency spectrum (45 min) ​

For the same feature, list the distinct pieces of data it touches (e.g. for a feed: the posts, the like-counts, the "seen" state, the user's profile snapshot). For each, decide where it sits on the consistency spectrum — does it need strong consistency, or is eventual fine? — and write one sentence justifying each placement in terms of what staleness would actually cost. If you placed everything at "strong," push yourself: which of these would genuinely be fine slightly stale, and what would you buy by relaxing it?

Deliverable ​

A one-page data design for a feature, containing: the dominant access patterns, a chosen data model (relational, document, or hybrid) with a one-line justification, the columns you'd index and why, and a consistency placement for each distinct piece of data with its justification. A reviewer will read it in about fifteen minutes and respond to one question: do the modeling, indexing, and consistency choices follow from the stated access patterns, or were they chosen by default?

Check yourself ​

Before you consider this chapter done, you should be able to:

  • Explain why data decisions are harder to reverse than code decisions, and why that argues for thinking them through early.
  • Choose between a relational and a document model for a given feature, justified by its relationships and access patterns.
  • Explain what an index actually is and state the trade-off it makes — without calling it magic.
  • Describe the consistency spectrum and place a few concrete pieces of data on it, justified by the cost of staleness.
  • Explain why replication is useful and what the sync-vs-async choice trades.

If any feel shaky, redo Exercise 1 on a second table — almost everything in this chapter becomes concrete the moment you reason from real access patterns.

What a strong answer demonstrates: a strong self-assessment shows the modeling, indexing, and consistency choices all following from the stated access patterns rather than from defaults — and it places each distinct piece of data on the consistency spectrum by what staleness would actually cost, not reflexively at "strong." The clearest signal you've understood the chapter is that you can justify relaxing consistency somewhere and name what that buys, while defending strong consistency exactly where a wrong answer would harm.

Going deeper (optional) ​

This chapter compressed a lot, most of it from one exceptional source:

  • Designing Data-Intensive Applications (Kleppmann) — the definitive modern treatment, and the backbone of this chapter. Chapter 2 covers data models, Chapter 3 storage and indexing, Chapter 5 replication, and Chapters 7 and 9 transactions and consistency in far more depth than we could. If any single topic here grabbed you, this is where to follow it.
  • Software Architecture: The Hard Parts, the chapter on pulling apart operational data — for how database-type selection and data ownership play out specifically when you're decomposing a system into services. We return to this in the synthesis and capstone chapters.

Primary sources (the foundational papers beneath the data ideas, if you want the theory on-ramp):

  • E. F. Codd, "A Relational Model of Data for Large Shared Data Banks" (Communications of the ACM, June 1970) — the paper that founded the relational model and normalization; everything in the modeling section descends from it.
  • Theo Härder & Andreas Reuter, "Principles of Transaction-Oriented Database Recovery" (ACM Computing Surveys, December 1983) — the paper that coined the ACID acronym, the precise source for the single-database transaction guarantees this chapter relies on.
  • Seth Gilbert & Nancy Lynch, "Brewer's Conjecture and the Feasibility of Consistent, Available, Partition-Tolerant Web Services" (ACM SIGACT News, 2002) — the formal proof of the CAP theorem (Brewer's original conjecture was his PODC 2000 keynote, "Towards Robust Distributed Systems"). This is where to go to see precisely why CAP's "C" means linearizability, not ACID's "C."

None of this is required to continue.

Key takeaways ​

  • Data outlives code, and data decisions are far harder to reverse — which is why they're worth thinking through early.
  • Every data decision is a bet on access patterns. Model, index, and distribute for the reads and writes you'll actually do.
  • Relational vs. document is a choice between flexible cross-cutting queries and fast whole-entity reads — driven by relationships and access patterns, not by which is "modern."
  • An index buys read speed and pays with write speed and storage. Index what you filter, join, and sort by; measure with query plans; don't index by reflex.
  • Consistency is a spectrum, not a switch. Place each piece of data on it by the cost of staleness. Distributing data forces consistency trade-offs a single database lets you avoid — every copy is a consistency decision.

Next chapter: Reliability Patterns — once data and calls cross machine boundaries, things fail in ways a single process never showed you: timeouts, partial failures, duplicated messages. We turn to the toolkit that keeps a system correct and standing when its parts misbehave — timeouts, retries, idempotency, and circuit breakers.