System Design Methodology and Trade-off Analysis Questions

Direct answer

Match the store to the shape of the access pattern and the guarantees the component actually needs, not to whichever store the team already operates: relational for data needing multi-record transactions and ad hoc joins, key-value for simple point lookups by a known key at very low latency, document for semi-structured records usually fetched whole, wide-column for very high write throughput on wide, sparse rows accessed by a known key plus a range, and graph for data whose primary queries traverse relationships across multiple hops rather than filtering on attributes.

Structured elaboration

Guiding factors, in the order a competent decision usually walks through them: what is the dominant query, a lookup, a scan, a join, or a traversal; how many hops does a typical query need to walk, zero or one hop rarely justifies a graph database, several hops usually does; how strict does the transactional guarantee need to be; and what is the actual sustained write rate and row shape, not just the current team's default toolchain.

Worked example

A single e-commerce platform, choosing per component rather than one store for everything:

• Order and payment ledger: relational, because it needs multi-row transactions across order, inventory, and payment state that must all succeed or fail together.
• User session cache: key-value, point lookups by session identifier, no joins, and very low latency is the only real requirement.
• Product catalog: document, records vary by product category and are typically read whole on a product page.
• Clickstream and event ingestion: wide-column, an extremely high write rate queried later by user identifier plus a time range, exactly the access pattern wide-column engines are built for.
• "Customers who bought this also bought" and fraud-ring detection: graph, because the actual query traverses relationships (co-purchase edges, shared payment instruments) several hops deep, which is expensive to express as repeated joins in a relational engine.

Trade-offs & pitfalls

• Defaulting to whichever store the team already runs, then discovering the real query pattern needs relationship traversal or very high wide-row write throughput, and bolting it onto the wrong engine, shows up later as application code re-implementing joins or graph-walks, or a single store buckling under a write pattern it wasn't designed for.
• Choosing a graph database for data with only shallow, one-hop relationships is over-engineering, a foreign key in a relational table is simpler and has better tooling for that case.
• Choosing a wide-column store for workloads that need ad hoc filtering across arbitrary attributes works against its design, wide-column stores are fast specifically because queries are restricted to key plus range, arbitrary attribute filtering is its weakest fit.
• The same logical dataset can legitimately live in more than one store at once, for example a relational system of record with a derived graph or search index kept in sync, when no single store serves every access pattern well, but that adds an explicit synchronization problem that needs an owner, not an assumption that it will stay consistent on its own.

Direct answer

Normalize when write correctness and storage efficiency matter most: each fact lives in exactly one place, so an update touches one row and there is no duplicate copy to drift out of sync. Denormalize when read speed matters most: copying a value into the table that needs it removes a join at read time, at the cost of extra storage and extra write work to keep every copy consistent. The decision is really about where you are willing to pay a cost: on the write path (normalized) or on the read path (denormalized).

Structured elaboration

What normalization buys you

• A single source of truth for each fact (a customer's name lives in one row in the customers table). Rename a customer once, and every order referencing that customer's ID sees the new name immediately, because nothing else stored a copy.
• No update anomalies: you cannot end up with two rows disagreeing about the same customer's e-mail address, because there is only one row.
• Smaller row sizes and less redundant storage, since each attribute is stored once.

What it costs

• Reads that need a full picture (an order plus the customer's name and the product's title) require joining across multiple tables. As the number of tables in the join grows, so does read latency and database load per request.

What denormalization buys you

• Fast reads: a single table scan or index lookup returns everything the page needs, no join required. This matters most for read-heavy, latency-sensitive paths (a product listing page, an order-history feed).
• Fewer round-trips and less join computation on the database, which matters at high read volume.

What it costs

• Duplicated data: the same fact (a product's name, a customer's e-mail) now lives in more than one row.
• Write amplification and staleness risk: change the source fact once, and every duplicate copy must also be updated, or the duplicates drift and become wrong. If you skip updating one copy, you now have silently inconsistent data.
• More total storage, since the same bytes are stored multiple times.

How to actually decide

1. Estimate the read:write ratio on the specific table or field in question, not the system as a whole. A field read a thousand times for every write is a strong denormalization candidate; a field written as often as it is read is not.
2. Ask how often the would-be-duplicated value actually changes. A product's category ID rarely changes; a live inventory count changes constantly. Denormalizing something that changes constantly multiplies your write cost and your staleness risk.
3. Ask how expensive staleness is if a duplicate briefly lags. A denormalized display name that is a few seconds stale is usually fine; a denormalized account balance is usually not.
4. Consider partial solutions before going fully one way: a materialized view or a cached read model gives you denormalized-shaped reads without hand-maintaining duplicate columns in the source tables, at the cost of a refresh lag you must define and tolerate.

Worked example

Take an orders schema. Normalized (third normal form): an orders table (order ID, customer ID, timestamp), an order_items table (order ID, product ID, quantity, unit price), a customers table, and a products table. Rendering an order-detail page means joining order_items to products (for the product name and image) and joining orders to customers (for the customer's name), a three- to four-way join.

Suppose the system processes 1,000,000 orders a month, averaging 3 line items per order, so 3,000,000 order_items rows are written per month. A normalized order_items row (order ID, product ID, quantity, unit price as fixed-width fields) is roughly 28 bytes. A denormalized version that also copies in the product name (about 24 bytes), product category (about 12 bytes), customer name (about 20 bytes), and customer e-mail (about 24 bytes) adds about 80 bytes per row:

$$28\ \text{bytes/row (normalized)} \quad\text{vs}\quad 28 + 80 = 108\ \text{bytes/row (denormalized)}$$

At 3,000,000 rows a month, that is:

$$3{,}000{,}000 \times 80\ \text{bytes} = 240{,}000{,}000\ \text{bytes} \approx 240\ \text{MB}$$

of pure duplicate data added every month, before counting index overhead or replication. That is the storage side of the cost. The write side shows up when a product gets renamed: if that product already appears in 50,000 historical order_items rows, a normalized schema needs a single row updated in products; a denormalized schema that copied the product name into order_items needs all 50,000 rows updated (or accepts that historical order rows show the old name, which is a legitimate choice for orders specifically, since an order should arguably show the name as it was at purchase time, not the current name).

$$50{,}000\ \text{rows updated} \quad\text{vs}\quad 1\ \text{row updated}$$

That last point is the real lesson: denormalizing an order line item's product name is often correct, not just a performance hack, because an order is a historical record and should not silently change when a product is renamed later. Denormalizing a customer's current e-mail address into the same row would be the wrong call, because you want that field to always reflect the customer's latest value, and a copy will drift.

Trade-offs & pitfalls

• Over-normalizing a read-heavy path (a product catalog page hit thousands of times a second) forces the database to redo the same multi-table join on every request, which is real, measurable load that a single denormalized read model would remove.
• Over-denormalizing a field that changes often multiplies write cost for a marginal read benefit, and creates a data-integrity bug class (stale duplicates) that is easy to miss in testing and expensive to debug in production.
• A common pitfall is denormalizing before measuring the actual read:write ratio, based on an assumption that reads are always dominant. Analytics and reporting schemas intentionally denormalize heavily (star-schema fact and dimension tables in an online analytical processing, OLAP, warehouse), because they are overwhelmingly read-heavy and batch-loaded; the live transactional path behind an online transaction processing (OLTP) system usually should not copy that pattern wholesale.
• The strongest senior answer treats this as a per-field decision, not a whole-schema philosophy: a single table can normalize some columns and denormalize others based on how each specific column is actually read and written.

Direct answer

Decide per field with a simple test: what does a user or the business lose if this field is read stale for a few seconds, and does that loss involve authorization, money, or identity? Email, password, and account status gate who can act as whom, so they get a linearizable (single, globally agreed order) read/write path even at a latency cost. Display name and avatar are cosmetic: a stale value for a few seconds costs nothing but a visual blip, so they get eventual, region-local, low-latency writes and reads. Defending the split means showing what making everything strong actually costs on the read path, not just asserting that it is safer.

Structured elaboration

Per-field decision table

Mechanism

This paragraph is implementation detail, useful to know by name but not required to follow the field-by-field argument made above it. Two logical stores per user: a small, strongly-consistent store (consensus-replicated, for example a Raft-based database, where Raft is an algorithm that gets a cluster of replicas to agree on the same order of writes, or a globally-consistent database) for the identity-critical fields, and a multi-region, eventually-consistent store (Dynamo-style or similar) for everything else. Reads compose a single user object from both stores, so only the strong-store portion pays the cross-region latency cost. Read-after-write for the strong fields comes from routing that specific read to the writer's region or the current leader; monotonic reads (once a client has seen a value, a later read never shows it an older one) for the weak fields come from a session token, not from the strong store.

Extending the framework: billing correctness without going fully strong

Billing state is the case that tempts people into "just make everything strong." Resist it: instead of a synchronous global commit for every billing event, use compensating transactions, an idempotent charge (safe to run the same charge request twice, say after a retry, without actually billing the customer twice) plus a defined reversal or refund path if a downstream step (fraud check, inventory hold) fails after the charge already happened. This gets you correctness (the ledger is right once reconciliation finishes) without paying the linearizable-everything latency tax on a field written far less often than it is read.

Defending the split with a number, not an opinion

The strongest defense against "why not just make it all strong" is quantifying what "all strong" costs on the read path, since profile reads vastly outnumber profile writes.

Worked example

Assume a region-local cache read costs 5 ms, and a linearizable read from the strong store (contacting a majority of replicas across 3 regions, with an illustrative one-way inter-region round-trip time (RTT) of 100 ms) costs roughly two one-way trips:

$$\text{strong-store read latency} \approx 2 \times 100 \text{ ms} = 200 \text{ ms}$$ $$\text{latency multiplier if every read used the strong path} = \frac{200 \text{ ms}}{5 \text{ ms}} = 40\times$$

If, say, 95% of profile reads only ever touch display-name or avatar fields (illustrative traffic mix, would come from real access logs), forcing all of them through the strong store means 95% of read traffic pays a 40x latency tax for a guarantee only the remaining 5% of fields ever needed. That is the number to put in front of someone asking why you didn't make everything strongly consistent.

Now the revenue-risk quantification (the second absorbed angle): the case for still investing in correctness on the billing fields, even though they don't get the fully linearizable treatment either.

$$\text{assumed error rate on a race-prone billing path} = 0.1\% = 0.001$$ $$\text{assumed volume} = 200{,}000 \text{ billing transactions/day at average value } \$50$$ $$\text{expected daily exposure} = 200{,}000 \times 0.001 \times 50 = \$10{,}000/\text{day}$$

Ten thousand dollars a day of exposure (illustrative; in practice pulled from real incident and error-rate data) is what justifies spending engineering time on compensating transactions for billing.

Trade-offs & pitfalls

• The strong store becomes a small, high-value target: shard it narrowly (identity fields only) so its lower throughput ceiling never becomes the bottleneck.
• Session tokens that carry the last-seen strong-store commit are what give read-your-own-writes on the critical fields without every read hitting the leader; skipping this is a common miss that reintroduces stale-password bugs.
• Pitfall: treating "eventual consistency" as a synonym for "no correctness work needed." The weak store still needs a conflict-resolution rule (last-writer-wins or a merge function), or two concurrent display-name edits silently lose one.
• Pitfall: treating billing as either fully strong or fully eventual instead of reaching for the third option, compensating transactions, which is usually the right cost and correctness balance for money-adjacent but not identity-adjacent fields.

Direct answer

Payment processing should favor consistency during a network partition, and product catalog browsing should favor availability, because the two operations have opposite costs when they go wrong: an inconsistent payment can create a real financial loss or a double charge, while a stale catalog page is a minor, self-correcting annoyance. The right lens is not "which service is more important" but "what does staleness or unavailability actually cost for this specific data," which is exactly why the same platform can, and should, make opposite choices for its two services.

Structured elaboration

Decision criteria, side by side

Metrics that would defend the split, if challenged

• Payment: commit latency (P95/P99, 95th/99th percentile), and abort/retry rate. A rising abort rate under partition is the system correctly refusing to guess; a rising rate of duplicate-charge incidents would mean the consistency posture failed.
• Catalog: replica lag (a staleness window measured in seconds) and cache hit rate. A growing staleness window is the visible cost of the availability-first choice, and it should have an agreed ceiling (a service-level objective, SLO) rather than being left open-ended.

Mechanism, named but not re-derived
Payment typically uses a majority-quorum write (a quorum is the minimum number of replicas that must agree before a read or write counts as successful) against a small number of strongly consistent replicas (or a single-leader transactional database); catalog typically uses asynchronous replication with read replicas and edge caching. The internals of quorum protocols and cache invalidation are their own topics; what matters here is that these are two different, deliberate consistency configurations applied to the same platform.

Worked example

Take a five-node deployment (N = 5) split across three data centers, and a partition that isolates 2 nodes from the other 3. Two pieces of notation carry the arithmetic below: W is how many replicas must acknowledge a write before it counts as done, and R is how many must respond to a read before it is returned to the caller; AP and CP name the two postures, AP meaning the system favors Availability over Consistency when the network Partitions, CP meaning it favors Consistency over Availability instead.

Catalog (AP): W = 1, R = 1. Either side can serve any single reachable node.

$\text{majority side: } 3 \ge 1, \qquad \text{minority side: } 2 \ge 1$

Both sides stay available. The risk: the two sides may accept conflicting updates to the same catalog item (say, a price change), which gets reconciled (for example, by last-write-wins on a timestamp) once the partition heals.

Payment (CP): majority-quorum writes, requiring W = ⌈(N+1)/2⌉ = 3 acknowledgments.

$W_{maj} = \left\lceil \frac{N+1}{2} \right\rceil = \left\lceil \frac{5+1}{2} \right\rceil = 3$

$\text{majority side: } 3 \ge 3 \; \Rightarrow \; \text{quorum reachable, writes continue}$

$\text{minority side: } 2 < 3 \; \Rightarrow \; \text{quorum unreachable, writes must be refused}$

The same partition event produces two different outcomes on purpose: the catalog stays available everywhere and quietly reconciles later; payment processing keeps working on the majority side and explicitly refuses new authorizations on the minority side, rather than risk two systems each thinking they alone authorized the same order.

The same reasoning generalizes to other service pairs on a platform. A shopping cart during a partition usually leans AP too: accepting an item add on whichever side is reachable and merging any duplicate or conflicting cart state once the partition heals costs less (in lost conversions) than blocking the add. An ML feature store splits the same way payments and catalog do: the online serving path leans AP (serve the last known feature value within a freshness window), while the offline training-data snapshot leans CP (a training run built from a partially-written snapshot silently corrupts the model, so it waits for a consistent point-in-time view).

Trade-offs & pitfalls

• Defending the split with an opinion ("payments feel important") instead of naming a concrete cost of staleness or downtime and a metric that would catch a violation of the chosen posture.
• Assuming the whole platform must share one CAP posture; a mature platform is a portfolio of per-service, sometimes per-operation, decisions.
• Choosing CP for payment but forgetting the user-facing failure path: what checkout shows when the minority side can't reach quorum matters as much as the backend behavior. A clear "please try again" beats a silent hang.
• Naming, without re-deriving, that idempotency keys (a unique identifier attached to a request so that retrying it after a timeout or failure cannot accidentally apply the same charge twice) and compensating transactions let a team take a calculated availability risk on payment writes without producing duplicate charges; that mechanism belongs to a different topic, but knowing it exists is part of a complete answer here.

Direct answer

A high-level architecture shows the system's scope: the major building blocks (client, API layer, service tier, datastore, cache, external dependencies), how they relate, and the non-functional constraints (scale, availability) that shaped them. A component-level design zooms into one of those blocks and specifies its interfaces, request/response schemas, data flows, and sequencing. You show the high-level view to stakeholders who need to understand what the system is and what it costs or risks; you show component-level design to the people who have to build, test, or integrate against one specific piece.

Structured elaboration

The reason both layers matter: the high-level view sets the strategy and the constraints everyone else has to work inside; the component-level view is what actually gets implemented, tested, and integrated. A good design doc keeps an explicit mapping from each high-level block down to its component-level detail, so a reviewer can move between the two without re-deriving context.

Worked example

Say you're designing a subscription billing feature. At the high level you'd draw: client apps, an API gateway, a billing service, a payments component, a database, and a message queue for async notifications, with an arrow showing the billing service calls out to a third-party payment processor. The one decision that belongs only at this level: whether billing lives inside the existing monolith or is split into its own service, because that choice affects deployment, on-call ownership, and the blast radius of an incident, not just this one feature.

At the component level, you'd zoom into just the billing service and produce: a sequence diagram for "create subscription" (client → billing service → payments component → processor → database write → event published), the exact request/response schema for the POST /subscriptions endpoint, and an entity-relationship diagram for the subscription and invoice tables. None of that detail belongs on the high-level diagram; it would bury the one decision (monolith vs separate service) that the high-level view exists to surface.

Trade-offs & pitfalls

• Showing component-level detail (full schemas, every retry path) to an executive or product stakeholder buries the one decision they actually need to weigh in on.
• Skipping the high-level view and jumping straight to component design risks locking in a boundary (a shared database, a synchronous call where an event would do) that is expensive to undo later, because it was never surfaced as a decision.
• A common weak answer just says "high-level is the big picture, low-level is the details" without naming a decision that is exclusive to one level; naming that decision is the signal an interviewer is listening for.
• Keep a living link between the two artifacts (a component-level design should reference which high-level block it belongs to) so the documentation doesn't drift apart as the system evolves.