A Quick Note: This Is Week 4 of 8
This is the fourth post in the eight-week From Foundations to FAANG-Ready series. Previous weeks covered scalability and load balancing (Week 1), distributed systems fundamentals (Week 2), and API design (Week 3). Here is the full series:
Week 1: System Design Fundamentals: Scalability, Latency, and Load Balancing
Week 2: Distributed Systems Fundamentals: Consistency, Replication, Sharding, and Queues
Week 3: API Design for System Design Interviews: REST, GraphQL, gRPC, and How to Choose
Week 4: Data Modeling and Database Design: SQL vs. NoSQL and How to Choose the Right One
Week 5: How to Draw a High-Level System Architecture That Impresses Interviewers
Week 6: Detailed System Design: How to Go Deep, Find Bottlenecks, and Discuss Trade-offs
Week 7: System Design Case Studies: Design a URL Shortener and Design a Twitter Feed
Week 8: System Design Case Studies: Design a Chat System and Design a Rate Limiter
Subscribe to get all eight weeks delivered to your inbox as they publish.
Every system design interview reaches the same moment. The requirements have been gathered, the scale has been estimated, and the interviewer asks how you would store the data. The candidate names a database and moves on.
The interviewer asks why. The candidate says it scales well or it handles unstructured data without being able to connect either claim to the specific data shape or access patterns of the system being designed.
This exchange reveals a gap between knowing database names and understanding database design.
The interviewer is not testing which databases you have heard of. They are testing whether you can look at a set of requirements, model the data correctly, and make a defensible database choice that follows from the data model and access patterns rather than from habit or familiarity.
This week covers data modeling and database design from the ground up. What data modeling actually is and why it comes before the database choice. How relational databases work and when they are the right tool.
How the major NoSQL families work and what each one is built for. And a decision framework that produces a defensible database choice for any system design problem, one that holds up when the interviewer pushes back.
What Is Data Modeling
Most engineers jump straight to the database choice before thinking clearly about the data. This is backwards.
The data model should drive the database choice, not the other way around.
Before you can choose a database, you need to understand what data the system needs to store, how that data is structured, and how it will be accessed.
Data modeling is the process of identifying the entities in the system, the attributes of each entity, and the relationships between entities.
An entity is a thing the system needs to track: a user, a post, an order, a product, a message.
Attributes are the properties of each entity: a user has a name, an email address, a created-at timestamp.
Relationships describe how entities connect to each other: a user has many posts, a post belongs to one user, a post has many comments, an order contains many products.
Getting the data model right before touching the database choice is what makes the rest of the design coherent. A system whose data model is wrong will fight its database in ways that are expensive and hard to fix after deployment.
Entities and Attributes
When modeling entities, the goal is to identify the data that genuinely needs to be stored versus data that can be derived.
A user’s age does not need to be stored if the birth date is stored, because age can be computed from birth date. Storing derived data creates a consistency problem: if the birth date is updated, the stored age becomes wrong unless it is also updated.
The rule is to store the source of truth and derive everything else.
Attributes have types and constraints.
A price is a decimal with a precision requirement.
A username has a maximum length and a uniqueness constraint. An email address has a format constraint and a uniqueness constraint. Modeling these correctly before designing the schema prevents bugs that are expensive to fix after the fact.
Relationships and Cardinality
Relationships between entities have cardinality, which describes how many instances of each entity can participate in the relationship. Understanding cardinality is essential for schema design because different cardinalities require different storage approaches.
One-to-one: each instance of entity A relates to exactly one instance of entity B and vice versa. A user has exactly one profile. A profile belongs to exactly one user. In a relational database, one-to-one relationships are typically stored in the same table or in separate tables with a foreign key.
One-to-many: each instance of entity A relates to many instances of entity B, but each instance of B relates to exactly one instance of A. A user has many posts. Each post belongs to exactly one user. In a relational database, the many side stores a foreign key pointing to the one side. The posts table has a user_id column pointing to the users table.
Many-to-many: each instance of entity A can relate to many instances of entity B and vice versa. A student can enroll in many courses. A course can have many students. In a relational database, many-to-many relationships require a junction table with foreign keys to both sides.
Getting cardinality right is what drives the schema design.
A one-to-many relationship implemented as a many-to-many junction table is over-engineered.
A many-to-many relationship stored as a comma-separated list in a single column is a design error that will cause serious problems as the data grows.
Normalization: The Right Level of Structure
Normalization is the process of organizing data to reduce redundancy and improve consistency.
The core idea is that each piece of information should exist in exactly one place in the database.
If a user’s email address is stored in both the users table and the orders table, and the user changes their email, both places must be updated. Missing one creates inconsistency.
The standard levels of normalization (called normal forms) encode increasingly strict rules about eliminating redundancy. In practice, the important thing to understand is not the formal rules but the principle: avoid storing the same fact in multiple places.
Denormalization is deliberately introducing redundancy to improve read performance. A report that needs a user’s name alongside their order data could join the users and orders tables on every read, or it could store the user’s name directly in the orders table. The join is correct but slow at scale.
The denormalized version is faster but requires updating the stored name in orders whenever the user’s name changes.
Normalization versus denormalization is a trade-off between write complexity (denormalization adds writes) and read performance (denormalization removes joins). The right point on this spectrum depends on the read-to-write ratio and the performance requirements of the specific queries.
SQL Databases: When Relationships and Transactions Matter
Relational databases have been the foundation of most software systems for fifty years.
Understanding precisely why, and precisely when they are the right choice, is what makes a SQL recommendation defensible.
How Relational Databases Work
A relational database stores data in tables with rows and columns. Every row in a table represents one instance of the entity. Every column represents one attribute. The schema defines what columns exist, what types they hold, and what constraints apply.
Foreign keys are the mechanism that enforces relationships between tables. A post row has a user_id column containing the ID of the user who wrote it. The database enforces that user_id must reference a valid row in the users table. This constraint is called referential integrity, and it is the relational database’s guarantee that the relationships between entities stay consistent.
Joins are the mechanism for querying across relationships. To get a user’s posts with the user’s name, you join the users and posts tables on the user_id field. The database assembles the combined rows according to the join condition. SQL’s ability to express complex joins across many tables in a single query is one of its most powerful features and one of its most important advantages over NoSQL databases.
ACID Transactions
ACID stands for Atomicity, Consistency, Isolation, and Durability. These are the four properties that a relational database guarantees for every transaction.
Atomicity means all operations in a transaction either succeed together or fail together. A bank transfer debiting one account and crediting another is atomic: either both changes happen or neither does. Partial completion is impossible.
Consistency means the database moves from one valid state to another valid state. Constraints, foreign keys, and uniqueness requirements are enforced on every transaction. A transaction that would violate any constraint is rejected entirely.
Isolation means concurrent transactions do not see each other’s intermediate state. Two simultaneous transactions behave as if they ran one after the other. This prevents a class of bugs called race conditions that appear when multiple operations run concurrently on shared data.
Durability means a committed transaction is permanent. Once the database acknowledges a commit, the data is written to durable storage and will survive a crash. The write-ahead log is the mechanism that makes durability possible, as covered in the database crash course.
ACID transactions are what make relational databases the right choice for financial systems, inventory management, and any domain where partial completion or concurrent corruption of data has serious consequences.
Indexing in Relational Databases
Indexes make queries fast by creating a separate data structure that the database can use to find rows matching a condition without scanning the entire table.
A query for a user by email address on a table with ten million rows would take seconds without an index and milliseconds with one.
The cost of an index is write overhead. Every insert, update, or delete must update all indexes on the affected table.
A table with five indexes pays five times the write cost for index maintenance. The right indexing strategy means indexing the queries the system actually runs, verifying that indexes are used, and removing unused ones.
Composite indexes cover multiple columns and are useful when queries filter by multiple conditions.
An index on user_id and created_at together can serve a query for all posts by a specific user ordered by date without a separate sort step. Understanding composite index column order, the leading column rule, is one of the most commonly tested database design details.
When SQL Is the Right Choice
SQL is the right choice when the data is structured and relational with clear entity relationships that queries need to traverse. It is the right choice when the system needs ACID transactions because partial completion has serious consequences. It is the right choice when query patterns are varied or not fully known in advance, because SQL’s query language handles ad-hoc questions flexibly.
It is the right choice when the team is small and operational simplicity matters, because relational databases are well-understood and have mature tooling.
The main scenario where SQL struggles is write scaling beyond a single primary.
All writes go through one machine, and when write throughput exceeds what one machine can handle, horizontal write scaling requires sharding, which is complex. For systems with enormous write volumes, this limitation is significant.
NoSQL Databases: When Scale, Flexibility, or Specific Patterns Win
NoSQL is not one thing. It is a family of databases that each make a different set of trade-offs in exchange for specific capabilities. Treating NoSQL as a single alternative to SQL misses the point entirely: different NoSQL databases are appropriate for completely different problems.
Key-Value Stores
A key-value store maps keys to values with no structure imposed on the values.
The three operations are get (retrieve the value for a key), put (store a value for a key), and delete (remove a key). These operations are extremely fast because the database does no query planning, no scanning, and no joining. It goes directly to the value.
Redis is the most widely used key-value store. It holds data in memory, making it the fastest option for cache-style workloads. DynamoDB is a managed key-value and document store that persists to disk and scales automatically.
Key-value stores win for session storage, user preferences, rate limit counters, leaderboards, and any workload where every access is a lookup by a known key. They lose when you need to find keys by anything other than the key itself: there is no efficient way to query all sessions that have been active in the last hour or all users with a specific preference.
Document Stores
A document store holds data as documents, typically JSON-like objects where each document can have a different structure.
Documents are retrieved by their ID or by querying their fields.
MongoDB is the most widely used document store.
Document stores win when each entity is naturally a self-contained record that is always read and written together, when record fields vary across instances, and when the data has nested structure.
A product catalog where different product categories have completely different attributes is a natural fit.
An electronics product has a voltage rating and a wattage. A clothing item has a size and a material. Forcing these into a relational schema requires either a single table with many nullable columns or a complex entity-attribute-value design. A document store handles them naturally.
Document stores lose when queries regularly need to combine data from multiple collections, because there is no native join mechanism. Cross-collection queries must be done in application code by fetching from multiple collections and joining in memory.
Wide-Column Stores
Wide-column stores like Cassandra and HBase organize data by a partition key with many columns per row.
The partition key determines which node stores the row, and rows within a partition are sorted by a clustering key.
Wide-column stores are designed for very high write throughput and very large data volumes. They scale horizontally by adding nodes, and writes go to the node responsible for the partition key without coordination.
This makes them excellent for time-series data, activity logs, and messaging systems where the workload is predominantly writes and reads are by partition key and time range.
The critical constraint is that the data model must be designed around the queries. Cassandra tables are often described as query-first design: you design the table structure based on the specific queries you need to serve rather than based on normalized entity relationships.
A query that cannot use the partition key for filtering requires scanning all partitions, which is prohibitively expensive.
Graph Databases
Graph databases store data as nodes and edges, optimized for traversing relationships. Neo4j is the most widely used example.
Graph databases win when the relationships between entities are as important as the entities themselves. Social networks (find users two degrees from this user), recommendation engines (find products bought by users similar to this user), and fraud detection (find accounts connected through suspicious transactions) are natural graph problems. These queries are extremely expensive in relational databases because they require self-joins or recursive queries. In a graph database they are first-class operations.
Graph databases are specialized and do not handle general data storage well. A system that needs to store and query records, with occasional graph traversal, is better served by a relational database with careful query design.
Time-Series Databases
Time-series databases like InfluxDB and TimescaleDB are optimized for data indexed by time: metrics, sensor readings, financial prices, and server performance data. They provide efficient compression of sequential numeric data, fast range queries by time, and built-in aggregation functions for computing averages, sums, and percentiles over time windows.
Time-series databases are appropriate for any system that needs to store and query time-indexed numeric data at scale. Monitoring systems, IoT platforms, and financial data feeds are the canonical use cases.
The Decision Framework
The decision framework has five questions asked in order. The answers narrow the field to the right database for the specific system.
Question 1: What Is the Shape of the Data?
Structured and relational data with clear relationships that queries need to traverse points toward SQL. Self-contained records with variable fields point toward document stores. Simple key-based lookups point toward key-value stores. Append-heavy time-indexed data points toward wide-column stores or time-series databases. Relationship-centric data where traversal is the primary query pattern points toward graph databases.
This is the most important question because it directly reflects what each database is optimized to store. A database whose storage model matches the data shape is faster, simpler to query, and easier to evolve than one whose model fights the data.
Question 2: Do You Need Transactions?
If the system requires multi-entity atomicity where partial completion leaves data in an invalid state, this strongly constrains the choice to relational databases or the few NoSQL databases that explicitly support multi-document transactions. A payment system where charging a card and crediting an account must succeed or fail together needs ACID transactions. A user activity log where each event is independent does not.
Question 3: What Are the Access Patterns?
The access patterns tell you what the database needs to be efficient at. If reads are always by a known key, a key-value store is ideal. If reads are complex queries joining multiple entities with filters, sorting, and aggregation, SQL’s query language is the right tool. If reads are always by partition key and time range, a wide-column store’s data model matches exactly.
Access patterns also reveal whether the system is read-heavy or write-heavy. Read-heavy workloads benefit from caching and read replicas. Write-heavy workloads might need a write-optimized database or a sharded architecture.
Question 4: What Is the Scale?
If the data volume or write throughput is within what a well-configured single machine can handle (which is larger than most engineers assume, often hundreds of thousands of writes per second for modern NVMe SSDs), a relational database with replication is often sufficient and simpler to operate.
If write volume or storage genuinely exceeds single-machine capacity, the database must scale horizontally. Key-value stores, wide-column stores, and document stores are designed for horizontal scaling. Relational databases can shard but it adds operational complexity.
Question 5: Does the System Need More Than One Database?
The most mature answer to many database questions is that different parts of the system use different databases, each optimized for its workload. The user profile is structured and relational, warranting PostgreSQL. The session store is pure key-based lookup, warranting Redis. The activity log is append-heavy time-series, warranting Cassandra. The search index warrants Elasticsearch.
This pattern is called polyglot persistence, and recognizing when it applies is a strong signal of senior database design thinking. Not every system needs it, and the added operational complexity of multiple databases must be justified by genuine workload differences. But for systems with diverse data needs, it is the right answer.
Putting the Decision Framework Together
A complete database decision in a system design interview sounds like this.
For an e-commerce order system: “The core data is highly relational. Orders belong to users, orders contain line items, line items reference products, and products have inventory. I need ACID transactions because charging a payment and decrementing inventory must succeed or fail together. Partial completion means charging a customer for something we cannot ship. I would use PostgreSQL. The query patterns include looking up orders by user, by status, by date range, all of which SQL handles cleanly. For scale, I would add read replicas for the read-heavy order history queries and add a cache in front of the product catalog which is frequently read and infrequently updated. If write volume eventually grows beyond what a single primary handles, I would shard by user ID so all of a user’s orders stay on one shard and the most common queries never need to cross shards.”
For a social media activity feed: “Each activity event is an independent record with a user ID and a timestamp. The primary read pattern is get the last one hundred events for a given user, which is a lookup by user ID and a time range. There are no joins required. Write volume is enormous because every action every user takes generates an event. I would use Cassandra with user ID as the partition key and timestamp as the clustering key. This means all events for one user are on one node, and the get last one hundred events query reads one partition in order with no scatter-gather. The write throughput scales linearly by adding nodes.”
Each answer names the choice, connects it to the data shape, justifies it against the access patterns, explains the transaction requirements, and addresses scale. This structure is what makes a database recommendation defensible under follow-up questioning.
Common Database Design Mistakes in Interviews
Understanding what to avoid is as valuable as understanding what to do.
Choosing the database before modeling the data. Candidates who say I would use MongoDB before describing the data shape have chosen a hammer before knowing what they need to build. The data model comes first.
Treating NoSQL as automatically more scalable. NoSQL databases are not universally more scalable than SQL. They are more scalable for specific workloads and make different trade-offs. A relational database with proper indexing, read replicas, and a cache handles enormous scale for read-heavy workloads. Reaching for NoSQL without a specific reason is not a scaling decision.
Ignoring the access patterns. A database chosen based on data shape alone without considering how that data will be accessed often fails to perform. The query patterns are as important as the data structure in the database choice.
Missing the transaction requirement. Candidates who propose a NoSQL database for a financial system without addressing how they would achieve transactional integrity are proposing a design that would corrupt data under failure conditions. ACID requirements must be identified early and drive the database choice.
Under-specifying the data model. Naming the database without describing any schema, entity relationships, or access patterns tells the interviewer nothing about whether you understand the data. At least sketch the key entities, their relationships, and the primary access patterns even if you do not enumerate every field.
Key Takeaways
Data modeling comes before the database choice. Identify the entities, their attributes, their relationships, and the cardinality of each relationship before deciding where to store the data.
Normalization reduces redundancy by ensuring each fact exists in one place. Denormalization deliberately introduces redundancy to improve read performance. The right balance depends on the read-to-write ratio and consistency requirements.
SQL databases win when data is structured and relational, when ACID transactions are required, and when query patterns are varied or not fully known in advance.
NoSQL is a family, not a single alternative: key-value stores for fast key lookups, document stores for flexible self-contained records, wide-column stores for high-volume write workloads with known access patterns, graph databases for relationship-traversal queries, and time-series databases for time-indexed numeric data.
The decision framework asks what shape the data is, whether transactions are required, what the access patterns are, what the scale demands, and whether different parts of the system warrant different databases.
Polyglot persistence, using different databases for different workloads within the same system, is the mature answer when workloads genuinely differ enough to justify the operational complexity of multiple databases.
The complete database answer names the choice, connects it to the data shape, justifies it against the access patterns, explains the transaction requirements, and addresses how it scales, in that order.
Next week in Week 5, we cover how to draw a high-level system architecture that impresses interviewers: how to translate requirements into components, how to connect them, what to include versus defer, and how to narrate the diagram as you draw it. Subscribe so Week 5 lands in your inbox when it publishes.
The same principle applies to retirement planning: goals before products.
Most clients pick SIPs or funds out of habit, never asking what the corpus needs to do.
Requirements first, then the instrument — that's the only defensible way.