NoSQL Data Modeling
Understand NoSQL databases — key-value stores, document databases, wide-column stores, and graph databases. Learn when and why to choose NoSQL over relational systems.
The Big Picture — Why NoSQL Exists
Relational databases are incredible — they've powered the internet for decades. But they have a fundamental limitation: they scale vertically (bigger machine) much better than horizontally (more machines). When you need to handle millions of writes per second, store petabytes of data, or model relationships that don't fit neatly into tables, relational databases start to struggle.
NoSQL databases were born from this pain. They sacrifice some of what makes relational databases great (strict schemas, ACID transactions, powerful joins) in exchange for what massive-scale systems need: , flexible data models, and tunable consistency.
The Storage Facility Analogy
A relational database is like a filing cabinet — everything has a strict folder structure, labels, and cross-references. It's organized and powerful, but when you have 10 billion documents, you can't just buy a bigger cabinet. NoSQL databases are different storage styles for different needs: Key-Value stores are lockers — you have a key, you open the locker, you get your stuff. Blazing fast, zero complexity. Document stores are filing folders — each folder contains a complete document (JSON). Folders can have different structures. No rigid schema. Wide-column stores are massive spreadsheets — optimized for writing and reading huge volumes of columnar data. Analytics and time-series love this. Graph databases are corkboards with strings — pins are entities, strings are relationships. Perfect when the connections between data ARE the data.
🔥 Key Insight
NoSQL doesn't mean "no SQL" — it means "not only SQL." The right question is never "SQL or NoSQL?" — it's "which data model fits my access patterns?" Many production systems use both: a relational database for transactional data and a NoSQL database for caching, search, or analytics.
SQL vs NoSQL Overview
Before diving into each NoSQL type, understand the fundamental differences between the relational and NoSQL worlds.
| Feature | SQL (Relational) | NoSQL |
|---|---|---|
| Data Model | Tables with rows and columns | Key-value, document, column-family, graph |
| Schema | Strict, predefined (ALTER TABLE to change) | Flexible, schema-on-read |
| Scaling | Vertical (bigger machine) | Horizontal (more machines) |
| Joins | Powerful, built-in (JOIN clause) | Limited or none — denormalize instead |
| Transactions | Full ACID | Varies — some offer ACID, most offer eventual consistency |
| Query Language | SQL (standardized) | Database-specific APIs |
| Best For | Complex queries, transactions, relationships | Scale, speed, flexible schemas, specific access patterns |
The Four Types of NoSQL
Key-Value
Locker: key → value
Document
JSON folders
Wide-Column
Sparse spreadsheets
Graph
Nodes & edges
Eventual Consistency — The Trade-off
Most NoSQL databases trade strong consistency for availability and partition tolerance (the ). This means: after a write, not all replicas may have the latest data immediately. Eventually, they converge — but there's a window where different nodes return different values.
🔒 Strong Consistency (SQL default)
- After a write, all reads return the new value
- Simpler to reason about
- Higher latency (must wait for all replicas)
- Lower availability during network partitions
🔄 Eventual Consistency (NoSQL common)
- After a write, reads may return stale data briefly
- Converges to latest value within milliseconds to seconds
- Lower latency (respond from nearest replica)
- Higher availability (system works during partitions)
💡 When Eventual Consistency Is Fine
A user's like count showing 4,999 instead of 5,000 for 200 milliseconds? Fine. A bank balance showing $500 instead of $300 after a withdrawal? Not fine. Match the consistency model to the business requirement.
Key-Value Stores
A key-value store is the simplest NoSQL database. It's a giant hash map: you give it a key, it gives you a value. No schemas, no queries, no joins — just blazing-fast lookups by key. The most common example is .
The Locker Room
A key-value store is a locker room. You have a key (locker number), you open it, you get whatever's inside. You can't search by what's inside the lockers — you must know the key. But opening a locker is instant, no matter how many lockers exist.
How It Works
SET user:42:session "eyJhbGciOiJIUzI1NiJ9..." → OK
GET user:42:session → "eyJhbGciOiJIUzI1NiJ9..."
SET product:99:price "29.99" → OK
GET product:99:price → "29.99"
SETEX rate_limit:ip:192.168.1.1 60 "47" → OK (expires in 60s)
GET rate_limit:ip:192.168.1.1 → "47"
Key design matters:
✅ user:42:session → namespaced, readable
❌ abc123 → meaningless, unmaintainable
The value can be anything:
→ String, JSON blob, binary data, serialized object
→ The database doesn't inspect or index the value
→ All queries are by key — that's it
Real-World Use Cases
Caching Layer
Cache database query results, API responses, or computed values. Redis sits in front of your database and serves repeated reads in microseconds instead of milliseconds.
Session Storage
Store user sessions (auth tokens, cart state) with automatic expiration. Every request checks Redis for the session — O(1) lookup, no database query.
Rate Limiting
Track request counts per IP or API key with TTL-based expiration. INCR + EXPIRE gives you a sliding window rate limiter in two commands.
Strengths
- ✅Sub-millisecond reads and writes (in-memory)
- ✅Simplest data model — zero learning curve
- ✅Horizontal scaling via sharding (partition by key)
- ✅TTL support — data expires automatically
- ✅Perfect for caching, sessions, counters, queues
Weaknesses
- ❌No complex queries — can't search by value
- ❌No relationships between keys
- ❌No secondary indexes (must know the exact key)
- ❌Limited data modeling — everything is flat
- ❌Memory-bound (Redis) — expensive for large datasets
| Database | Type | Storage | Best For |
|---|---|---|---|
| Redis | In-memory | RAM (with optional disk persistence) | Caching, sessions, real-time counters, pub/sub |
| Memcached | In-memory | RAM only (no persistence) | Pure caching (simpler than Redis) |
| DynamoDB | Disk-based | SSD (managed by AWS) | Serverless key-value at scale, single-digit ms latency |
| etcd | Disk-based | SSD (distributed) | Configuration storage, service discovery (Kubernetes uses it) |
🎯 Interview Insight
In system design interviews, Redis appears in almost every design. When you mention caching, session storage, or rate limiting, say "I'd use Redis as a key-value cache here" and explain the key design pattern. Interviewers love seeing that you think about key naming conventions.
Document Stores
A document store saves data as self-contained documents — typically JSON or BSON. Each document can have a different structure. There's no rigid schema enforced by the database, so your data model can evolve without migrations.
The Filing Cabinet with Flexible Folders
A relational database is a spreadsheet — every row must have the same columns. A document store is a filing cabinet where each folder contains a complete document. One folder might have a 2-page resume, another a 50-page contract. They're in the same cabinet but have completely different structures. You can add new fields to new documents without changing existing ones.
How It Works
// User document — self-contained, nested data
{
"_id": "user_42",
"name": "Alice Smith",
"email": "alice@example.com",
"address": {
"street": "123 Main St",
"city": "Seattle",
"state": "WA",
"zip": "98101"
},
"orders": [
{
"id": "order_101",
"total": 59.99,
"status": "delivered",
"items": [
{ "product": "Keyboard", "qty": 1, "price": 59.99 }
]
}
],
"preferences": {
"theme": "dark",
"notifications": true
}
}
// Product document — different structure, same collection
{
"_id": "product_99",
"name": "Mechanical Keyboard",
"price": 59.99,
"category": "Electronics",
"specs": {
"switches": "Cherry MX Blue",
"layout": "TKL",
"backlight": "RGB"
},
"reviews_count": 1247,
"avg_rating": 4.6
}
Data Modeling: Embed vs Reference
The biggest decision in document modeling: do you embed related data inside the document, or store a reference (ID) and look it up separately?
| Strategy | How | Pros | Cons | Use When |
|---|---|---|---|---|
| Embed | Nest related data inside the document | Single read gets everything, no joins needed | Data duplication, document size limits (16MB in MongoDB) | Data is read together, 1:1 or 1:few relationships |
| Reference | Store the related document's ID, fetch separately | No duplication, smaller documents | Multiple reads needed (no joins), application-level joins | Data is large, many:many relationships, updated independently |
Real-World Use Cases
User Profiles
Each user has different fields — some have addresses, some have social links, some have preferences. A flexible schema handles this naturally without NULL columns.
Product Catalogs
A keyboard has 'switches' and 'layout'. A shirt has 'size' and 'color'. Different products have different attributes — document stores handle this without schema changes.
Content Management
Blog posts, articles, pages — each with different metadata, tags, embedded media. The flexible schema matches the varied nature of content.
Strengths
- ✅Flexible schema — add fields without migrations
- ✅Nested data — one read gets the full object
- ✅Horizontal scaling — sharding is built-in
- ✅Developer-friendly — JSON maps directly to code objects
- ✅Good for evolving data models (startups, MVPs)
Weaknesses
- ❌Data duplication — embedded data is copied, not referenced
- ❌No joins — cross-document queries require application logic
- ❌Consistency — updating duplicated data requires updating everywhere
- ❌Document size limits (16MB in MongoDB)
- ❌Complex aggregations are slower than SQL
| Database | Format | Managed Options | Best For |
|---|---|---|---|
| MongoDB | BSON (binary JSON) | MongoDB Atlas | General-purpose document store, most popular |
| DynamoDB | JSON-like | AWS (fully managed) | Serverless, single-digit ms at any scale |
| Firestore | JSON | Google Cloud (fully managed) | Mobile/web apps, real-time sync |
| CouchDB | JSON | Self-hosted / Cloudant | Offline-first apps, multi-master replication |
🎯 Interview Insight
When an interviewer asks "why not just use MongoDB for everything?" — explain that document stores struggle with relationships. If your data is highly relational (orders → products → categories → suppliers), a relational database with JOINs is far more efficient than doing application-level joins across documents.
Column-Family (Wide-Column) Databases
Wide-column stores organize data into rows and column families, but unlike relational tables, each row can have a different set of columns. They're optimized for writing and reading massive volumes of data across distributed clusters.
The Sparse Spreadsheet
Imagine a spreadsheet with 1 billion rows. In a relational database, every row must have every column (even if most are NULL). A wide-column store is a sparse spreadsheet — each row only stores the columns it actually has. Row 1 might have columns A, B, C. Row 2 might have columns A, D, F. No wasted space, no NULLs. And the spreadsheet is split across 100 machines, each handling a portion of the rows.
How It Works
Table: sensor_readings
Partition Key: sensor_id
Clustering Key: timestamp (sorted)
Row key: sensor_42
┌────────────────────────────────────────────────────────┐
│ timestamp │ temperature │ humidity │ pressure │
├────────────────────┼─────────────┼──────────┼──────────┤
│ 2025-01-15T10:00 │ 22.5 │ 45 │ │
│ 2025-01-15T10:01 │ 22.6 │ │ 1013.2 │
│ 2025-01-15T10:02 │ 22.4 │ 46 │ 1013.1 │
└────────────────────────────────────────────────────────┘
Row key: sensor_99
┌───────────────────────────────────────────────┐
│ timestamp │ temperature │ wind_speed │
├────────────────────┼─────────────┼────────────┤
│ 2025-01-15T10:00 │ 18.2 │ 12.5 │
│ 2025-01-15T10:01 │ 18.3 │ 11.8 │
└───────────────────────────────────────────────┘
Key concepts:
→ Partition key (sensor_id) determines which node stores the data
→ Clustering key (timestamp) determines sort order within a partition
→ Each row can have different columns (sparse)
→ Reads within a partition are sequential (fast)
→ Writes are append-only (extremely fast)
Data Modeling: Query-First Design
In relational databases, you model the data first and write queries later. In wide-column stores, you design the schema around your queries. You ask: "What queries will I run?" and build the table structure to serve those queries efficiently. This is called .
✅ Queries Wide-Column Handles Well
- "Get all readings for sensor_42 in the last hour"
- "Get the latest 100 events for user_7"
- "Write 500K metrics per second from IoT devices"
- Time-range scans within a partition
❌ Queries That Don't Fit
- "Find all sensors where temperature > 30" (full scan)
- "Join sensor data with user profiles" (no joins)
- "Count distinct sensors per region" (aggregation)
- Ad-hoc queries on non-key columns
Strengths
- ✅Extreme write throughput (append-only, distributed)
- ✅Linear horizontal scaling (add nodes, capacity grows)
- ✅Time-series data is a natural fit
- ✅No single point of failure (masterless architecture in Cassandra)
- ✅Handles petabytes of data across hundreds of nodes
Weaknesses
- ❌Query-first design — must know access patterns upfront
- ❌No ad-hoc queries (can't just SELECT * WHERE anything)
- ❌No joins, no subqueries, limited aggregations
- ❌Data duplication is expected (denormalize for each query)
- ❌Steep learning curve — data modeling is non-intuitive
| Database | Architecture | Consistency | Best For |
|---|---|---|---|
| Apache Cassandra | Masterless (peer-to-peer) | Tunable (per-query) | IoT, time-series, high-write workloads |
| Google Bigtable | Master-based (managed) | Strong | Analytics, ML pipelines, large-scale storage |
| HBase | Master-based (on Hadoop) | Strong | Hadoop ecosystem, batch analytics |
| ScyllaDB | Masterless (Cassandra-compatible) | Tunable | Drop-in Cassandra replacement, lower latency |
🎯 Interview Insight
When an interviewer asks about logging, metrics, or IoT data, wide-column stores are the answer. Say: "I'd use Cassandra with sensor_id as the partition key and timestamp as the clustering key. This gives me fast writes and efficient time-range queries within a partition."
Graph Databases
Graph databases store data as nodes (entities) and edges (relationships). Unlike relational databases where relationships are implicit (foreign keys + JOINs), graph databases make relationships first-class citizens — stored, indexed, and traversed directly. Each hop across a relationship is regardless of total data size.
The Corkboard with Strings
Imagine a corkboard. Each pin is a person (node). Strings connect pins to show relationships: 'Alice FOLLOWS Bob', 'Bob WORKS_AT Google', 'Google LOCATED_IN Mountain View'. To find 'friends of friends of Alice', you just follow the strings — two hops. In a relational database, that's a multi-table JOIN that gets exponentially slower with each hop. In a graph database, it's a constant-time traversal.
How It Works
// Create nodes
CREATE (alice:Person {name: "Alice", age: 30})
CREATE (bob:Person {name: "Bob", age: 28})
CREATE (google:Company {name: "Google", founded: 1998})
// Create relationships (edges)
CREATE (alice)-[:FOLLOWS]->(bob)
CREATE (alice)-[:WORKS_AT {since: 2020}]->(google)
CREATE (bob)-[:WORKS_AT {since: 2019}]->(google)
// Query: Who does Alice follow?
MATCH (alice:Person {name: "Alice"})-[:FOLLOWS]->(friend)
RETURN friend.name
→ "Bob"
// Query: Friends of friends (2 hops)
MATCH (alice:Person {name: "Alice"})-[:FOLLOWS]->()-[:FOLLOWS]->(fof)
WHERE fof <> alice
RETURN fof.name
// Query: Shortest path between Alice and someone
MATCH path = shortestPath(
(alice:Person {name: "Alice"})-[*]-(target:Person {name: "Eve"})
)
RETURN path
// This is where graph DBs shine:
// In SQL, each "hop" requires another JOIN
// In a graph DB, traversal depth doesn't affect query complexity
Real-World Use Cases
Social Networks
'People you may know' = friends of friends. 'Mutual connections' = shared edges. These are graph traversals that would require recursive JOINs in SQL.
Recommendation Engines
'Users who bought X also bought Y' = traversing purchase relationships. Netflix, Amazon, and Spotify use graph-based recommendations.
Fraud Detection
'Is this account connected to known fraudulent accounts within 3 hops?' Graph traversal finds suspicious patterns that are invisible in tabular data.
Graph vs Relational — The Depth Problem
| Query | SQL (Relational) | Graph DB |
|---|---|---|
| Direct friends | 1 JOIN — fast | 1 hop — fast |
| Friends of friends | 2 JOINs — OK | 2 hops — fast |
| 3 degrees of separation | 3 JOINs — slow | 3 hops — fast |
| 6 degrees of separation | 6 JOINs — extremely slow or impossible | 6 hops — still fast |
| Shortest path | Recursive CTE — very slow | Built-in algorithm — optimized |
Strengths
- ✅Relationship queries are O(1) per hop (index-free adjacency)
- ✅Multi-hop traversals don't degrade with data size
- ✅Natural model for connected data (social, knowledge, networks)
- ✅Pattern matching is built into the query language
- ✅Shortest path, centrality, community detection built-in
Weaknesses
- ❌Not ideal for simple CRUD without relationships
- ❌Horizontal scaling is harder than document/KV stores
- ❌Smaller ecosystem and community than SQL or MongoDB
- ❌Aggregations (SUM, COUNT, AVG) are not its strength
- ❌Overkill if your data doesn't have meaningful relationships
| Database | Query Language | Scaling | Best For |
|---|---|---|---|
| Neo4j | Cypher | Vertical (clustering in Enterprise) | General-purpose graph, most popular |
| Amazon Neptune | Gremlin / SPARQL | Managed, horizontal | AWS-native graph workloads |
| ArangoDB | AQL (multi-model) | Horizontal | Graph + document in one DB |
| Dgraph | GraphQL-like (DQL) | Horizontal | Distributed graph at scale |
🎯 Interview Insight
When an interviewer asks about social networks, recommendation systems, or fraud detection, graph databases are the answer. Say: "I'd use Neo4j for the social graph because multi-hop relationship queries (friends of friends, mutual connections) are constant-time traversals, whereas SQL JOINs degrade exponentially with depth."
End-to-End Decision Scenarios
The real skill isn't knowing what each database does — it's knowing which one to pick for a specific problem. Here are four scenarios with the reasoning behind each choice.
You're building a caching layer for an e-commerce site
Which database would you use?
Answer: Redis (key-value store). Cache product pages, search results, and session data. Key design: 'product:42:detail' → JSON blob, 'session:user_7' → session token, 'search:shoes:page1' → cached results. Set TTL of 5 minutes for product data, 30 minutes for sessions. Redis gives sub-millisecond reads, reducing database load by 80-90%. If Redis goes down, the system still works (just slower) — cache is not the source of truth.
You're building a product catalog for an e-commerce platform
Which database would you use?
Answer: MongoDB (document store). Products have wildly different attributes — a laptop has RAM and CPU specs, a shirt has size and color, a book has ISBN and author. A relational schema would need dozens of nullable columns or an EAV pattern. MongoDB lets each product document have its own structure. Embed reviews (1:few) inside the product document for single-read performance. Reference the seller (1:many) by ID since seller data is shared across products.
You're building a logging and metrics system for 10,000 IoT sensors
Which database would you use?
Answer: Cassandra (wide-column store). Each sensor writes a reading every second — that's 10K writes/second, 864M rows/day. Partition key: sensor_id. Clustering key: timestamp DESC. This gives you fast writes (append-only) and efficient time-range queries ('last hour of readings for sensor_42'). Cassandra scales linearly — add more nodes as sensors grow. The masterless architecture means no single point of failure. You'd never use a relational database here — it can't handle this write volume.
You're building a social network with 'People You May Know'
Which database would you use?
Answer: Neo4j (graph database) for the social graph, plus PostgreSQL for user profiles and posts. The 'People You May Know' feature requires finding friends-of-friends who aren't already connected — a 2-hop traversal with filtering. In SQL, this is a self-JOIN on a massive table that gets exponentially slower. In Neo4j, it's a constant-time traversal regardless of network size. Use PostgreSQL for the structured data (profiles, posts, settings) and Neo4j specifically for relationship queries.
💡 The Polyglot Persistence Pattern
Most production systems use multiple databases. An e-commerce platform might use: PostgreSQL for orders and transactions (ACID), MongoDB for the product catalog (flexible schema), and sessions (speed), and for product search (full-text). This is called polyglot persistence — using the right database for each job.
Trade-offs & Decision Making
Every database choice is a trade-off. Here are the key decisions you'll face in interviews and real systems.
SQL vs NoSQL — When to Choose Each
| Scenario | Choose | Why |
|---|---|---|
| Financial transactions | SQL (PostgreSQL) | ACID guarantees — money can't be eventually consistent |
| User sessions / caching | Key-Value (Redis) | Sub-ms reads, TTL expiration, simple access pattern |
| Product catalog with varied attributes | Document (MongoDB) | Flexible schema, nested data, no rigid columns |
| IoT sensor data (1M writes/sec) | Wide-Column (Cassandra) | Extreme write throughput, linear horizontal scaling |
| Social graph / recommendations | Graph (Neo4j) | Multi-hop relationship traversals in constant time |
| Full-text search | Search engine (Elasticsearch) | Inverted indexes, relevance scoring, fuzzy matching |
| Simple CRUD with relationships | SQL (PostgreSQL) | JOINs, constraints, mature tooling — don't overcomplicate |
Consistency vs Availability
| Trade-off | Strong Consistency | Eventual Consistency |
|---|---|---|
| Guarantee | Read always returns latest write | Read may return stale data briefly |
| Latency | Higher (wait for all replicas) | Lower (respond from nearest replica) |
| Availability | Lower during partitions | Higher during partitions |
| Use when | Money, inventory, auth | Likes, views, feeds, analytics |
| Examples | PostgreSQL, DynamoDB (strong mode) | Cassandra, DynamoDB (eventual mode) |
Denormalization Impact
📐 Normalized (SQL approach)
- No data duplication
- Updates happen in one place
- Requires JOINs to assemble data
- Read performance depends on JOIN complexity
- Best for: write-heavy, consistency-critical systems
📋 Denormalized (NoSQL approach)
- Data is duplicated across documents/rows
- Updates must propagate to all copies
- Single read gets everything (no JOINs)
- Read performance is predictable and fast
- Best for: read-heavy, latency-sensitive systems
🎯 Interview Framework
When asked "which database would you use?" — never just name a database. Say: "It depends on the access pattern. If we need [X], I'd choose [Y] because [Z]. The trade-off is [T], which is acceptable here because [reason]." This shows you think in trade-offs, not absolutes.
Interview Questions
Conceptual, scenario-based, and comparison questions you're likely to encounter.
Q:When would you use NoSQL over SQL?
A: When the data doesn't fit neatly into tables (varied product attributes → document store), when you need extreme write throughput (IoT metrics → wide-column), when you need sub-millisecond reads (caching → key-value), or when relationships ARE the data (social graph → graph DB). But if you need ACID transactions, complex JOINs, or your data is naturally relational — stick with SQL. Most systems use both.
Q:Why are JOINs difficult in NoSQL?
A: NoSQL databases are designed for horizontal scaling — data is distributed across many machines. A JOIN requires combining data from different locations, which means network hops between machines. In SQL, the database engine optimizes JOINs internally. In NoSQL, you'd need to fetch from multiple partitions and combine in application code — slow and complex. That's why NoSQL favors denormalization: store the data together so one read gets everything.
Q:What database would you use for a social graph?
A: A graph database like Neo4j. Social features like 'People You May Know' (friends of friends), 'Mutual Connections' (shared edges), and 'Degrees of Separation' (shortest path) are all graph traversals. In SQL, each 'hop' requires another JOIN — 6 degrees of separation means 6 JOINs on a billion-row table. In a graph DB, traversal is O(1) per hop regardless of total data size. I'd use the graph DB specifically for relationship queries and PostgreSQL for structured user data.
You're designing a real-time analytics dashboard for a SaaS product
Which databases would you use and why?
Answer: Redis for real-time counters (active users, events/second) — sub-ms reads, atomic increments. Cassandra for raw event storage — handles millions of writes/second, time-range queries for 'events in the last hour'. PostgreSQL for user accounts and billing — ACID transactions for payments. This is polyglot persistence: each database handles what it's best at.
A developer says 'Let's use MongoDB for everything'
What's wrong with this approach?
Answer: MongoDB is great for flexible schemas and document-oriented data, but it struggles with: (1) Transactions across multiple documents (improved but still not as robust as PostgreSQL). (2) Complex relationships — if your data has many-to-many relationships with JOINs, you'll end up doing application-level joins that are slower and harder to maintain. (3) Aggregations at scale — SQL databases with proper indexes handle complex analytical queries better. Use MongoDB where it shines (catalogs, profiles, content) and SQL where relationships and transactions matter.
Your Cassandra queries are slow
What's likely wrong?
Answer: You're probably querying against non-partition-key columns. Cassandra is designed for queries that hit a specific partition (WHERE partition_key = X). If you're doing a full table scan (SELECT * WHERE non_key_column = Y), Cassandra has to check every node — this is an anti-pattern. The fix: redesign your table around your query. If you need to query by a different column, create a second table with that column as the partition key. In Cassandra, you denormalize and duplicate data to serve each query pattern.
Common Mistakes
These mistakes are common in interviews and in production. Each one has caused real performance disasters.
Using NoSQL for everything
'NoSQL is modern, SQL is old' — this is wrong. Teams choose MongoDB for a system with complex transactions and relationships, then spend months building application-level JOINs and transaction logic that PostgreSQL gives you for free. NoSQL solves specific problems; it's not a universal replacement.
✅Start with the access pattern. If your data is relational and you need ACID transactions, use SQL. Reach for NoSQL only when SQL's limitations actually hurt you — scale, schema flexibility, or specific access patterns.
Ignoring data modeling in NoSQL
'NoSQL is schemaless, so I don't need to think about data modeling.' Wrong. NoSQL requires MORE careful data modeling than SQL — because you can't fix bad models with JOINs later. A poorly designed Cassandra table means full table scans. A poorly embedded MongoDB document means 16MB limits and update nightmares.
✅In document stores: decide embed vs reference for every relationship. In wide-column stores: design tables around queries (query-first design). In key-value stores: design key naming conventions. Schema-on-read doesn't mean no-schema.
Misunderstanding eventual consistency
Teams choose an eventually consistent database for financial data, then discover that a user's balance shows $500 after a $200 withdrawal — because the read hit a stale replica. Or they choose strong consistency everywhere and wonder why latency is high and availability is low during network issues.
✅Match consistency to the business requirement. Money, inventory, and auth need strong consistency. Likes, views, feeds, and analytics can tolerate eventual consistency. Many databases (DynamoDB, Cassandra) let you choose per-query.
Choosing the wrong type of NoSQL
Using MongoDB (document store) for time-series IoT data when Cassandra (wide-column) is purpose-built for it. Using Redis (key-value) for complex queries when you need a document store. Using a graph database for simple CRUD with no relationship queries.
✅Key-value → simple lookups, caching, sessions. Document → flexible schemas, nested data, varied attributes. Wide-column → high write throughput, time-series, append-heavy. Graph → relationship-heavy queries, social networks, recommendations. Pick the type that matches your access pattern.
Not considering polyglot persistence
Teams try to force one database to do everything. A single PostgreSQL instance handles transactions, caching, full-text search, and analytics — and struggles at all of them. Or a single MongoDB handles everything including financial transactions that need ACID.
✅Use the right tool for each job. PostgreSQL for transactions, Redis for caching, Elasticsearch for search, Cassandra for metrics. Connect them through your application layer. This is how every large-scale system works — Netflix, Uber, and Amazon all use 5+ database types.