Data Modeling

In system design interviews, data modeling is where you define what your system stores and how it's organized. It's one of the first concrete decisions you make—typically right after clarifying requirements—and it shapes everything that follows: your API design, query patterns, and scaling strategy.

This page covers how to approach data modeling in interviews, when to choose SQL vs NoSQL, and how to design schemas that support your system's requirements.

Data Modeling in the Interview

Data modeling usually happens in Phase 2 of your interview (see Interview Framework), after you've gathered requirements but before diving into architecture. The goal is to identify your core entities and their relationships.

A solid data model:

• Captures all entities from your requirements (users, posts, orders, etc.)
• Defines relationships between entities (one-to-many, many-to-many)
• Supports your access patterns — how will you query this data?

Core Entities and Relationships

Start by identifying the nouns in your requirements—these become your entities. Then determine how they relate to each other.

Example: Social Media Platform

Entities:

• Users
• Posts
• Comments
• Likes
• Followers

Relationships:

• A user has many posts (one-to-many)
• A post has many comments (one-to-many)
• A user can like many posts; a post can have many likes (many-to-many)
• Users can follow other users (many-to-many, self-referential)

Example: E-Commerce System

Entities:

• Users
• Products
• Orders
• Order Items
• Payments

Relationships:

• A user has many orders (one-to-many)
• An order has many order items (one-to-many)
• An order item references one product (many-to-one)
• An order has one payment (one-to-one)

SQL vs NoSQL: Making the Right Choice

This is one of the most common questions in system design interviews. The key isn't picking the "correct" answer—it's demonstrating that you understand the trade-offs and can justify your decision.

Default to SQL

For most interview problems, relational databases (SQL) should be your default. Here's why:

• Most problems have clear entities with relationships (users, posts, orders)
• SQL databases handle complex queries naturally with JOINs
• ACID transactions ensure data consistency
• The technology is mature and well-understood
• PostgreSQL and MySQL scale further than most people think

Choose SQL when:

• Data has clear relationships requiring JOINs
• You need ACID transactions (payments, inventory)
• Query patterns are complex or evolving
• Data integrity is critical

-- Example: Fetching a user's recent orders with items
SELECT o.id, o.created_at, p.name, oi.quantity
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.user_id = 123
ORDER BY o.created_at DESC
LIMIT 10;

When NoSQL Makes Sense

NoSQL databases aren't better or worse—they're optimized for different access patterns. Consider NoSQL when you have specific requirements that relational databases handle poorly.

Choose NoSQL when:

• You need horizontal write scaling beyond what a single SQL instance handles
• Data is naturally document-shaped (nested, variable structure)
• Access patterns are simple (key-value lookups)
• You're willing to denormalize for read performance

NoSQL Categories

Schema Design Principles

Once you've chosen your database type, you need to design your schema. The principles differ significantly between SQL and NoSQL.

SQL Schema Design

Normalization reduces data redundancy by splitting data into related tables. The goal is to store each piece of information once.

-- Normalized schema
users (id, name, email)
posts (id, user_id, content, created_at)
comments (id, post_id, user_id, content, created_at)

Benefits of normalization:

• No duplicate data to keep in sync
• Smaller storage footprint
• Updates only need to change one place
• Referential integrity through foreign keys

Denormalization intentionally duplicates data to optimize read performance. You trade storage and write complexity for faster reads.

-- Denormalized: store author name directly on posts
posts (id, user_id, author_name, content, created_at)

Now you can display posts without JOINing to the users table—but if a user changes their name, you need to update it in multiple places.

Foreign Keys and Referential Integrity

Foreign keys enforce relationships between tables:

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  content TEXT,
  created_at TIMESTAMP
);

Benefits:

• Prevents orphaned records (no posts referencing deleted users)
• Database enforces data integrity automatically
• Makes relationships explicit in the schema

Trade-off: Foreign key checks add overhead on writes. At extreme scale, some companies drop them and enforce integrity in application code. In interviews, mentioning this trade-off shows depth.

NoSQL Schema Design

NoSQL schema design is fundamentally different: design for your access patterns first.

In SQL, you normalize and then write queries to get the data you need. In NoSQL, you start with the queries you'll run and design your data structure to support them efficiently.

Example: User profile with posts (Document DB)

{
  "user_id": "123",
  "name": "Alice",
  "email": "alice@example.com",
  "posts": [
    {
      "post_id": "p1",
      "content": "Hello world",
      "created_at": "2024-01-15T10:30:00Z",
      "likes_count": 42
    }
  ]
}

This denormalized structure lets you fetch a user and their posts in a single read—but updating a post requires updating the entire user document.

Key principles for NoSQL:

• Embed data that's accessed together
• Duplicate data to avoid cross-document queries
• Design partition keys for even data distribution
• Accept eventual consistency where appropriate

Indexing Basics

Indexes make queries fast by avoiding full table scans. Understanding when and how to use them is essential for system design.

How Indexes Work

Without an index, finding a user by email requires scanning every row:

SELECT * FROM users WHERE email = 'alice@example.com';
-- Without index: O(n) - scan all rows
-- With index: O(log n) - B-tree lookup

When to Add Indexes

Add indexes for columns that appear in:

• WHERE clauses (filtering)
• JOIN conditions
• ORDER BY clauses

-- Common indexes for a posts table
CREATE INDEX idx_posts_user_id ON posts(user_id);      -- Filter by author
CREATE INDEX idx_posts_created_at ON posts(created_at); -- Sort by time

Index Trade-offs

Common Patterns

Handling Many-to-Many Relationships

Many-to-many relationships require a join table (also called a junction or association table):

-- Users can follow other users
CREATE TABLE follows (
  follower_id INTEGER REFERENCES users(id),
  following_id INTEGER REFERENCES users(id),
  created_at TIMESTAMP,
  PRIMARY KEY (follower_id, following_id)
);

-- Query: Who does user 123 follow?
SELECT u.* FROM users u
JOIN follows f ON u.id = f.following_id
WHERE f.follower_id = 123;

Storing Hierarchical Data

For tree structures (comments with replies, org charts), you have several options:

Adjacency List (simplest):

comments (id, parent_id, post_id, content)  -- parent_id references another comment, NULL for top-level

Materialized Path (faster reads):

comments (id, path, post_id, content)  -- path = "1/4/7" means comment 7 under comment 4 under comment 1

Time-Series Data

For data indexed by time (metrics, events, logs):

• Partition by time (daily/weekly tables or partitions)
• Use wide-column stores (Cassandra) or time-series databases (TimescaleDB) for high write volume
• Consider retention policies (delete old data automatically)

Quick Reference

Database Selection Flowchart

Interview Checklist

When presenting your data model:

• Identified all core entities from requirements
• Defined relationships (one-to-many, many-to-many)
• Chose SQL vs NoSQL with justification
• Considered key access patterns
• Mentioned relevant indexes
• Addressed any denormalization decisions

Example: URL Shortener Data Model

-- Simple and effective
urls (
  short_code VARCHAR(10) PRIMARY KEY,  -- "abc123"
  long_url TEXT NOT NULL,
  user_id INTEGER REFERENCES users(id),
  created_at TIMESTAMP,
  expires_at TIMESTAMP,
  click_count INTEGER DEFAULT 0
)

-- Index for user lookups
CREATE INDEX idx_urls_user_id ON urls(user_id);

Example: Chat Application Data Model

users (id, name, avatar_url)
conversations (id, name, is_group, created_at)
conversation_members (conversation_id, user_id, joined_at)
messages (id, conversation_id, sender_id, content, created_at)

-- Key indexes
CREATE INDEX idx_messages_conversation ON messages(conversation_id, created_at);
CREATE INDEX idx_members_user ON conversation_members(user_id);

What Interviewers Look For

1. Systematic approach — Do you identify entities methodically from requirements?
2. Justified decisions — Can you explain why SQL or NoSQL, not just pick one?
3. Awareness of trade-offs — Normalization vs denormalization, indexes vs write performance
4. Practical thinking — Do your access patterns match your schema design?

You don't need to write perfect SQL in an interview. What matters is demonstrating that you can think through data requirements, make reasonable choices, and articulate the trade-offs clearly. Spend 3-5 minutes on your data model, then move on—you can always refine it as your design evolves.