Data Modeling

Data modeling is step 2 in the Delivery Framework, where you identify the core entities your system works with. But it extends throughout the interview. During high-level design, you'll add fields to your entities as each request flow reveals what data you need. During deep dives, you'll revisit your model for indexing, denormalization, and sharding.

This lesson teaches you to think about data the way interviewers expect: start minimal, evolve as needed, and make modeling choices that align with your access patterns.

Start with Core Entities

Your core entities come directly from your functional requirements. If users can post tweets and follow each other, your entities are User, Tweet, and Follow. Don't overthink this. Spend two minutes listing the obvious nouns, then move on.

At this stage, don't specify every field. List just the entity names. You'll add fields incrementally as you design each API endpoint and trace data flows through the system.

Modeling Relationships

Most system design problems involve three types of relationships. Recognizing them quickly helps you choose the right storage strategy.

One-to-Many

A user has many tweets. An order has many line items. This is the most common relationship. Model it with a foreign key, where the "many" side stores the ID of the "one" side. tweets.user_id references the user who posted it.

One-to-many relationships are straightforward in relational databases. In document stores, you have a choice: embed the child documents inside the parent (good when you always read them together) or reference them by ID (good when children are accessed independently).

Many-to-Many

Users follow other users, students enroll in many courses, and a product can belong to several categories at once. Many-to-many relationships require a join table (also called a junction table or association table) that stores pairs of IDs.

One-to-One

A user has one profile. An order has one payment. One-to-one relationships can be modeled by putting all fields in one table, or by splitting into two tables linked by a shared primary key. Split when one side has significantly different access patterns or security requirements. A common case is separating user credentials from profile data.

Normalization vs Denormalization

Normalization means storing each piece of data in exactly one place, with references (foreign keys) connecting related data. This eliminates duplication and ensures consistency. Update a user's name in one place and it's correct everywhere.

Denormalization means duplicating data across tables to avoid expensive joins. Store the author's name directly on each tweet, so you don't need to join the users table every time you read a feed. This makes reads faster but makes writes more complex. When the user changes their name, you need to update it everywhere.

When discussing denormalization in interviews, always mention the consistency trade-off: "I'll store the username on each tweet for read performance. The downside is that if a user changes their name, we need to update all their tweets. We can do that asynchronously with a background job since the feed can tolerate a short delay."

Design for Access Patterns

The single most important principle of data modeling for system design: your data model should be shaped by how you read and write data. How the data is conceptually organized matters far less.

Start by listing your key access patterns. They come directly from your API endpoints. For each endpoint, ask: what data do I need to return, and what query produces it?

This is the key skill interviewers are testing: can you see that a particular access pattern will be expensive, and adjust your data model or architecture accordingly? Start with the simple normalized model, identify the expensive queries, then denormalize or cache strategically.

Indexing

An index is a data structure that makes lookups fast by column value, at the cost of slower writes and additional storage. Without an index, the database must scan every row to find matches (O(n)). With a B-tree index, lookups are O(log n).

Index the columns you filter and sort by. If your feed query filters by user_id and sorts by created_at, you need an index on (user_id, created_at). A composite index like this serves both the filter and the sort in a single index scan.

In interviews, you don't need to design a complete indexing strategy upfront. But when discussing a specific query during high-level design or deep dives, mention the relevant index: "This query filters by user_id and sorts by timestamp, so I'd add a composite index on (user_id, created_at)."

Relational vs Document Models

A full SQL vs NoSQL comparison gets its own lesson. At the data modeling level, the key difference is how you handle relationships.

The interview heuristic: if your entities have clear relationships and you need transactions across them, go relational. If your data is naturally hierarchical and you almost always read/write it as a complete unit, consider a document model. When in doubt, go relational. It's more flexible and interviewers won't question it.

Evolving Your Model

Your data model should evolve throughout the interview rather than being frozen at the start. During Step 2 (Core Entities), list 3-4 entity names like User, Tweet, and Follow without worrying about fields yet. In Step 4 (High-Level Design), add key fields as each API flow touches the data: "The tweet needs a user_id, text, and created_at." Note the table structure and primary indexes. Then in Step 5 (Deep Dives), denormalize for performance, add composite indexes, introduce caching layers, and discuss sharding keys: "I'll shard tweets by user_id so all of a user's tweets are co-located."

Interviewers want to see exactly this incremental approach. It shows that real requirements and access patterns drive your data model rather than guesswork.