Data Modeling in System Design: Choosing SQL, NoSQL, and the Right Schema
A practical reference for choosing data models from access patterns, transaction boundaries, scale, and schema maturity. Compare relational databases with four NoSQL families, design and normalize an order model, decide when denormalization is justified, and defend a polyglot architecture by naming its ownership and operational costs.
Why the Schema Is a High-Leverage Systems Decision
A schema can turn a small product change into a storage migration, a query rewrite, and a consistency problem at the same time. Renaming an API field is mostly a caller-coordination problem; changing the shape of data already stored in a large retained table is different because the work is tied to the records that already exist. The larger the retained dataset and the more readers depend on it, the less forgiving the change becomes.
The deeper cost is that your schema records assumptions about how the product will be queried. A design that works for “show me one user’s profile” may fight back when the product adds “show me a personalized feed assembled from everyone this user follows.” Relationships that were cheap to represent can become expensive to traverse at read time. The schema is therefore not just storage layout; it is a constraint on the access patterns, latency budget, and scaling strategy you can support without redesigning the data.
Twitter’s early status-update infrastructure is a useful example of that pressure. Its original model treated tweets and follows as normalized relational data. To build a home timeline, the system had to fan out through those relationships and assemble the feed, making the JOIN the critical path as usage grew. The eventual answer was not merely a better query: timelines were precomputed and stored in a cache or NoSQL-style system so reads could retrieve a prepared result. A published case study describes this kind of transition as a “hybrid persistence architecture” using “both relational and NoSQL technologies”.
Design a platform-scale distributed rate limiter55 min
Critique: find the failure modes in a given design45 min
The read workload, not the database label, drives the model change.
Follow the workload-driven shift from normalized relationship reads to precomputed timeline reads.
Before writing a single CREATE TABLE, walk through four questions: what entities and relationships exist, which access patterns must be fast, where multi-entity consistency matters, and how the data volume and read/write mix may change. Those answers determine whether you should preserve relationships for flexible queries, shape data around a narrow lookup, or plan a separate read model. The point is not to predict every future feature; it is to make today’s assumptions explicit before they become an expensive constraint.
CHECK YOUR UNDERSTANDING
You are designing a feature where users follow other users and need a personalized feed. What data model would you reach for first, and which questions would you answer before deciding?
SHOW ANSWERHIDE ANSWER
Start by mapping users, follows, and posts, then enumerate the reads: one user’s follows, recent posts from those users, and the complete personalized feed. Next ask whether the feed must be strongly consistent with a new follow or post, whether reads or writes dominate, and how the relationship and post volumes are expected to grow. A normalized relational model is a reasonable starting point because it preserves the relationships and supports changing queries. If profiling shows that assembling the home timeline is the hot path, introduce a precomputed timeline read model while defining how updates keep it consistent.
What the Relational Model Gives You for Free
A relational model separates entities into tables. Each row represents one occurrence, and keys identify it; a foreign key stores a reference rather than copying the referenced data. The engine can enforce that reference, so an order item cannot point at a product or order that does not exist. You get one authoritative product name or price instead of reconciling duplicated values across every order.
The order schema makes the relationship explicit. products owns product attributes, orders identifies the purchase, and order_items links orders to products. Define the references between these relations, and choose the key structure for order_items according to the product's requirements.
Model products, orders, and order_items as separate relations, with order_items carrying the references needed to associate products with orders. A receipt can then reconstruct the required view by joining those relations. Adding a discount-code column to orders is a clean relational schema change; the exact SQL type and syntax are implementation-specific.
The receipt view follows the stored references from orders through order_items to products. That is the important relational move: store each fact once, then reconstruct the view you need at read time. Because SQL supports joins, aggregations, and ad-hoc queries, you can ask new questions over these relations without first reshaping every stored record.
Foreign-key relationships let a relational query reconstruct an order from separate tables.
Follow the two foreign-key relationships from orders through order_items to products: the join path rebuilds a receipt from stored-once facts.
A schema change can also remain local to the owning table. Adding a discount-code column to orders changes the order representation without duplicating that value into every item or product row. The exact SQL type and syntax depend on the implementation.
Transactions and scale
For operations that must change multiple rows together, a relational database provides ACID guarantees: atomicity, consistency, isolation, and durability. Moving money between two accounts illustrates the boundary. Debit one account and credit the other inside one transaction; atomicity prevents only one side from committing, while isolation keeps concurrent operations from observing an incomplete transfer. The database commits the unit as a whole or rolls it back.
The usual scaling path is vertical: provision a larger server. Microsoft's guidance describes relational systems in which writes go to a primary and reads can be routed to secondaries; replication designs vary by system. Horizontal partitioning through sharding is possible; it distributes data across nodes, yet increases operational overhead and makes joins, transactions, and referential integrity more difficult across those pieces.
CHECK YOUR UNDERSTANDING
Why does the order schema keep product data in `products` instead of copying it into every `order_items` row?
SHOW ANSWERHIDE ANSWER
A single authoritative row avoids update anomalies and lets the foreign key enforce that each item refers to an existing product. The trade-off is that a receipt reconstructs the view with joins.
NoSQL Means Four Different Modeling Bets
NoSQL is not one data model. It is a group of non-relational stores that make different access patterns cheap. Before choosing one, name the operation that must be fast: a lookup by one key, fetching a whole hierarchical object, scanning a time-ordered slice, or traversing relationships. The store's shape follows that operation; query flexibility, transaction scope, and how much redundancy you manage follow from the same choice.
Four families, four access shapes
A key-value store maps one key to one value. Redis and DynamoDB in simple key-value use fit sessions, caches, and feature flags because the request already knows the key. In exchange, you get little query flexibility beyond that lookup: searching by an unmodeled attribute is not the store's natural operation.
A document store keeps an entity and its metadata together in a hierarchical JSON-based document. MongoDB and Firestore fit a user profile whose preferences are naturally nested and usually fetched with the profile. You shape the document around the read, rather than splitting every nested object into separately joined records.
A wide-column store represents related data as dynamic columns. Cassandra and HBase are suited to massive write throughput and time-series-style access, but they require query-first modeling. Cassandra's practical rule is: model your tables around your queries. For user activity filtered by user_id and a timestamp range, make user_id the partition key and place the time dimension in the access path. If the product later needs activity by a different dimension, create another table for that query. The duplicated data is intentional; each table is a read shape, not a universal representation of the entity.
A graph database makes nodes and edges first-class. Neo4j is a fit when the question is primarily about relationships: finding fraud rings, following social connections, or exploring recommendation graphs. The important distinction is not that graph data is merely stored in a different syntax; traversing connected entities is the operation the model is built to represent.
CHECK YOUR UNDERSTANDING
A Cassandra engineer says, “Model your queries, not your entities.” What does that mean, and how does it differ from a typical Postgres schema approach?
SHOW ANSWERHIDE ANSWER
Start by listing the exact reads, such as user activity for one user_id over a timestamp range. In Cassandra, choose the partition key and clustering shape to make that read direct, then create a separate table for a different access pattern and intentionally duplicate the needed fields. In Postgres, you would more commonly model the entities and relationships once, preserve them with relational constraints, and use SQL queries and indexes to support varied access patterns. Cassandra makes predictable query paths the schema's organizing principle.
Worked Example: Normalize First, Then Earn the Right to Denormalize
A reporting dashboard is a good place to start normalized. Suppose each page needs order details, customer information, and product information. Keep those facts in orders, customers, products, and order_items rather than copying customer names and product prices into every order row. The model separates entities, records relationships with keys, and gives you one authoritative place to update each fact.
Check the order-items table
Use the composite key (order_id, product_id) for order_items: one row represents one product on one order. Apply the normal-form checks directly. 1NF means each cell contains one atomic value and there are no repeating groups, so quantity is valid as one value; a column such as product_ids containing a list would violate 1NF. 2NF asks whether every non-key column depends on the whole composite key. quantity depends on both order_id and product_id, because the quantity is specific to that product on that order. product_name and product_price depend only on product_id, not on the whole key, so keeping them in order_items violates 2NF. Move them to products. 3NF removes transitive dependencies: non-key columns must depend only on the key. If orders contains both customer_id and customer_email, the email depends on customer_id, not directly on order_id; keep it in customers instead.
Keep the normalized entities in separate relations: orders, customers, products, and order_items. The model separates order and customer facts, stores product facts independently, and uses order items to represent the relationships needed by the receipt and reporting reads. Choose the exact fields, types, keys, and constraints for the product's requirements.
If the dashboard reads orders, customers, products, and order_items, the normalized path involves four relations and typically three join operations; measure the actual query plan before changing the model. That path is flexible and preserves one source of truth, but it is also the path you should measure. Add indexes that support the dashboard's actual filters and joins before changing the model.
Earn the denormalized read path
The denormalized relation removes the joins required by the measured normalized query; calculate the reduction from the actual query plan. Use this optimization only when profiling shows that the join path remains the hot path after you have exhausted appropriate indexing options. The reporting or analytics table should contain the fields the dashboard reads, while the normalized tables remain the source of truth.
That optimization transfers complexity to writes and refreshes. A product-price change, customer update, or order correction must reach the denormalized representation according to its update process. The normalized tables remain the source of truth; the reporting copy is a performance structure whose freshness and repair behavior you must own. Starting with the copy would hide those costs before you know that the joins are the problem.
CHECK YOUR UNDERSTANDING
An `order_items` table has the composite primary key `(order_id, product_id)`. It also stores `quantity`, `product_name`, and `product_price`. Which columns violate 2NF, and what goes wrong if you leave them there?
SHOW ANSWERHIDE ANSWER
quantity depends on the whole key because it describes how many units of a particular product are on a particular order. product_name and product_price depend only on product_id, so they violate 2NF. Leaving them there duplicates product facts across orders; changing a product requires updating multiple rows, and missing one creates inconsistent names or prices.
Choosing the Model from the Workload
Choose the model from the workload, not from the database category. Ask four questions in order: what access patterns must the system serve, whether one operation must update multiple entities atomically, how the read/write workload will grow, and how quickly the schema is still changing. The answers expose both the fit and the price: query flexibility can cost operational simplicity, while scale-out can move partitioning and consistency work into your application.
Use these questions in order, then assign each responsibility to the model that fits it.
Decision dimension
Prefer SQL when
Prefer NoSQL when
Access-pattern breadth
Queries are varied or unknown; ad-hoc queries, aggregations, and JOINs matter. Price: schema and indexes must support broader query work.
Access patterns are narrow and known; point lookups dominate. Price: query flexibility is limited and tables or documents follow the queries.
Transaction scope
One transaction must update multiple related entities with ACID guarantees. Price: distributed writes and sharding add operational complexity.
A write fits within one database partition or can tolerate eventual consistency. Price: the application owns more consistency and reconciliation logic.
Scale trajectory
Growth is primarily vertical, or read replicas can absorb read load. Price: horizontal sharding increases operational overhead and makes joins, transactions, and referential integrity more costly.
Write volume and data size require horizontal scale-out across nodes. Price: partitioning and replication make key design and operations central concerns.
Schema maturity
The domain and relationships are stable enough to benefit from a fixed schema and referential integrity. Price: large changes become migration work.
The schema is evolving quickly and flexible schema is a real product requirement. Price: weaker structure can shift validation and data-integrity work into the application.
Data shape/ownership
Complex relationships and transactional data need one authoritative owner. Price: read-heavy paths may need indexes, read replicas, or later denormalized projections.
Data is naturally hierarchical, graph-shaped, or append-heavy, and a query-specific model fits. Price: intentional duplication or multiple stores require explicit ownership and consistency management.
Read the price column first
The most important comparison is transaction scope. If a payment, trip, or inventory change must update several related records as one indivisible operation, SQL is usually the safer owner because the relational model provides ACID transactions and referential integrity. You pay for that boundary when data is sharded: joins, transactions, and integrity checks become harder to coordinate. A NoSQL model is a better fit when a write naturally belongs to one partition, or when the workflow can tolerate eventual consistency. The trade is not free performance; it is more application-level validation, reconciliation, and ownership.
A ride-sharing split
A ride-sharing system should not force every responsibility into one store. Let Postgres own trips, drivers, and payments, where transactional integrity matters. Let Redis own ephemeral driver location, where the dominant operation is a high-frequency point lookup. Let Cassandra or DynamoDB own append-heavy trip event logs, where the workload is time-series-shaped and must scale to massive volume. Each store has an explicit owner; caches and projections can be rebuilt from that authority rather than becoming competing sources of truth.
This is the practical meaning of polyglot persistence. A production system may use Postgres for transactional data, Redis for hot-read caching, and Elasticsearch for search. The design question is not which database wins globally. It is which model owns each data set, which queries it must answer, which consistency boundary it participates in, and what operational burden you accept to get that fit.
CHECK YOUR UNDERSTANDING
Your team says, “Just use MongoDB; it’s more flexible.” What does that flexibility cost, and when is it worth paying?
SHOW ANSWERHIDE ANSWER
Flexibility reduces the pressure to keep every record in one fixed shape, which is valuable when the product schema is genuinely evolving or the data is naturally hierarchical. The cost is weaker structure: validation and data-integrity work can move into the application, and narrow query patterns may limit later ad-hoc queries. Pay that cost when the access patterns are known, the object is usually fetched as a unit, or schema flexibility is a real product requirement. Do not choose it merely because the label sounds faster; choose it when its data model and scale path match the workload.
Failure Mode: Redundant Copies Drift Apart
Denormalization is a deliberate performance trade-off: you pre-join data at write time so a read can fetch a reporting result without repeating the expensive join. The read gets simpler, but the write now has more than one destination. The source row remains authoritative, while each denormalized copy becomes another consistency obligation.
Consider a product price copied into an order-reporting table. A price change updates the product row, then should update the reporting copy. If the second write is omitted, times out, or fails after the first write commits, the database contains two answers: the product table has the new price, while the report still has the old one. A dashboard can therefore produce a receipt total or sales metric that disagrees with the transactional record without either query being syntactically wrong.
A successful source write and a missed derived write leave the system with divergent values.
Follow the two branches from the product update: the source row receives the new value, while the missed reporting branch preserves the old value and creates divergent reads.
Make the extra write explicit
Treat the source update and every derived update as one workflow, not as an incidental side effect hidden in application code. The implementation must define what happens when one destination succeeds and another does not: retry the failed update, record it for asynchronous repair, or rebuild the derived table from the source of truth. Define how the system detects and repairs stale derived data; do not require a version or update-timestamp field unless the chosen implementation includes one.
Keep the canonical row authoritative and make the denormalized table disposable: it should be possible to backfill or rebuild it from canonical data. Monitor freshness and reconciliation, not only query latency. A useful invariant is that the copied product identifier and value match the source for the state represented by the copy. If that invariant fails, stop treating the report as current and expose the stale state instead of silently presenting it as fact.
CHECK YOUR UNDERSTANDING
A denormalized reporting copy is faster to read but sometimes disagrees with the source row. What should you check before trusting the report?
SHOW ANSWERHIDE ANSWER
Check which row is authoritative, compare the copied data with the source for the state the report claims to represent, and inspect the update or repair path for a partial failure. The optimization is acceptable only when the system can detect, retry, reconcile, or rebuild missed updates.
Failure Mode: A Live Schema Becomes a Migration Project
A schema change becomes a production failure when a product requirement changes the access pattern the existing tables were built to serve. An API rename can often be handled at the boundary; a schema change may require a table split, a new column, a new index, or an entirely new read path. On a large retained table, that is not a local code edit: existing readers and writers must keep working while old data is reshaped and new data follows the replacement model.
The failure usually unfolds in stages. A new feature first exposes the mismatch—for example, a query that previously loaded one entity now needs a separately filtered collection. You add compatibility logic so both representations can be understood, then migrate the live data. During the migration, writes can arrive for rows that have already moved, rows that have not moved, and rows being updated concurrently. If the application switches reads before the backfill is complete, it can return incomplete results; if it switches writes without a compatibility path, old readers can fail or silently miss new data.
A product change creates parallel compatibility and migration work before the updated access path can become authoritative.
Follow the two branches from the changed access pattern: compatibility work keeps old and new clients working while the live data migration catches up, after which reads and writes can move to the updated schema.
Treat the migration as an operational project rather than a DDL command. Use an expand-and-contract migration: add the new representation, support the transition, validate the migrated data, and remove the old path after traffic has moved. Keep the schema change under version control and test it against a production-like dataset; the migration's cost is not only the backfill, but also extra code paths, write amplification, validation, and rollback planning.
The practical trigger for this planning is scale: AWS describes data modeling as a blueprint for the information an organization collects, relates, stores, and analyzes, while production guidance warns that schema decisions are hardest to undo at scale and that table rewrites are expensive. Record the old and new access patterns, define which representation owns writes during the transition, and make completion measurable before you cut over.
How to Defend a Data Model in a Design Conversation
Start with the workload, not the database name. State the entities and relationships, then name the queries that matter: point lookups, range reads, feeds, joins, aggregations, or append-only events. Define the transaction boundary next— which changes must succeed or fail together—and describe the scale trajectory: read-heavy, write-heavy, bursty, or likely to grow across partitions. Finally, say how mature the schema is. A fast-changing product may value flexibility; a stable domain with strong relationships may benefit more from explicit constraints.
That split is defensible only when each store has one clearly defined responsibility and you state the accepted cost: duplicated data, asynchronous synchronization, extra operational ownership, or weaker cross-store transactions. Caches and projections should be rebuildable from their authoritative data rather than becoming competing copies of the whole domain.
The question that exposes shallow designs
Ask: “Which query becomes the failure mode if this model is wrong, and how will we detect it?” Someone who has operated the system will connect a model to a concrete symptom: a join that consumes the latency budget, a partition that receives disproportionate load, a feed that requires fan-out, or a stale denormalized projection. They will also explain the escape hatch—an index, a read model, a partitioning change, a replica, or a migration—and the consistency gap it introduces.
The weakest answer starts with “use SQL” or “use NoSQL,” then retrofits a workload to the slogan. Replace it with four sentences: these are my access patterns; these changes must be atomic; this is my scale and partitioning risk; this is how much schema evolution I expect. Then name the store, its owner, the operational cost you accept, and the failure mode you will measure.
CHECK YOUR UNDERSTANDING
What is missing from an answer that names a database but does not name its queries or transaction boundary?
SHOW ANSWERHIDE ANSWER
It has not shown that the model fits the workload. Without access patterns, transaction boundaries, scale trajectory, and schema maturity, the choice is a category preference rather than a defensible design.
KEY TAKEAWAYS
Choose a data model from the reads and writes the system must serve, not from the database category.
Relational schemas preserve relationships, constraints, and flexible queries; NoSQL schemas make specific access shapes cheap in exchange for narrower queries or more application-owned consistency.
Normalize first to establish authoritative data, then denormalize only for a measured hot path and treat every copy as a consistency obligation.
A defensible polyglot design assigns one owner to each data set and names the synchronization, partitioning, and operational costs.