Project: Choose the Right Database

Build the mental model

This is the capstone project, and it deliberately asks for no schema at all. Instead, it exercises the decision-making side of everything the course covered.

SQL vs NoSQL - the core tradeoffs
Database-systems-landscape - what each category is good at
SQLite - the case for a small embedded engine
Vector databases - storing and searching embeddings

A content-heavy blog with authors, posts, comments, and tags needs multi-table joins and referential integrity that stay correct as content grows — precisely the strength SQL-vs-NoSQL gave to relational databases.

A real-time chat app needs to write messages fast under a shape that keeps changing release to release — the flexible-write case given to document databases.

A caching and session layer needs no relationships at all, only extremely fast key lookups — exactly why key-value stores get their own category rather than a lesser SQL substitute.

A RAG application needs both ordinary application data and similarity search over embeddings, so the architecture needs a vector index working alongside the usual store, not replacing it.

Type first, product second

Naming the right category for each case, and explaining why, is the actual skill this whole course has been building toward.

text

CHOOSE THE RIGHT DATABASE
-------------------------
CHOOSE THE RIGHT DATABASE
-------------------------
SCENARIO                           RECOMMENDED TYPE
--------------------------------------------------------
Blog: authors/posts/comments/tags   -> Relational (SQL)
  needs multi-table JOINs, integrity

Chat app: fast, evolving messages   -> Document (NoSQL)
  needs flexible schema, high writes

Cache / session layer               -> Key-Value store
  needs simple, very fast lookups

RAG app: app data + embeddings      -> Relational/Document
                                        + Vector database
  needs similarity search on top of normal data

Connect it to a real scenario

Ask the same question for every scenario before naming a database type: what does a typical query against this data actually need to do?

Scenario	What the access pattern needs
Content-heavy blog	Multi-table joins across authors, posts, comments, tags with strong integrity -> Relational (SQL) database
Real-time chat app	Fast writes on a message shape that keeps changing -> Document database (NoSQL)
Cache / session layer	Only simple, very fast key lookups, no relationships -> Key-value store
RAG application	Ordinary app data plus similarity search over embeddings -> Relational/Document store + vector database

Identify the dominant need

Find the single most important access pattern for each scenario: relations, flexible writes, caching, or vector search.

Map it to the right lesson

Connect each need to the lesson that explains it: SQL-vs-NoSQL, the landscape lesson, or vector databases.

Pick the type

Choose a category (relational, document, key-value, vector), not a specific product.

Justify it in one sentence

State why the scenario needs that type in a single sentence.

State it in one sentence

If you cannot state the reason a scenario needs a given type, you have not actually finished choosing it.

The access pattern, not habit or popularity, should decide the database type.
Key-value, document, relational, and vector stores each fit a different shape of query.
Some real systems legitimately need more than one database type at once.

Try the working example

javascript

function chooseDatabaseType(scenario) {
  if (scenario.needsVectorSearch) {
    return {
      type: "Relational/document store + vector database (e.g. Postgres + pgvector)",
      reason: "app data needs relations, but similarity search over embeddings needs a vector index alongside it"
    };
  }
  if (scenario.needsComplexRelations) {
    return {
      type: "Relational (SQL) database",
      reason: "joins across authors, posts, comments, and tags need referential integrity and multi-table JOINs"
    };
  }
  if (scenario.needsSimpleKeyValueCache) {
    return {
      type: "Key-value store (e.g. Redis)",
      reason: "sub-millisecond reads/writes on simple key lookups matter more than relationships"
    };
  }
  if (scenario.needsFlexibleFastWrites) {
    return {
      type: "Document database (NoSQL)",
      reason: "flexible, fast-changing message shape and high write throughput fit a schema-less document model"
    };
  }
  return {
    type: "Relational (SQL) database",
    reason: "default to SQL when no specialized access pattern dominates"
  };
}

const scenarios = {
  blog: {
    needsComplexRelations: true,
    needsFlexibleFastWrites: false,
    needsSimpleKeyValueCache: false,
    needsVectorSearch: false
  },
  chat: {
    needsComplexRelations: false,
    needsFlexibleFastWrites: true,
    needsSimpleKeyValueCache: false,
    needsVectorSearch: false
  },
  cache: {
    needsComplexRelations: false,
    needsFlexibleFastWrites: false,
    needsSimpleKeyValueCache: true,
    needsVectorSearch: false
  },
  rag: {
    needsComplexRelations: false,
    needsFlexibleFastWrites: false,
    needsSimpleKeyValueCache: false,
    needsVectorSearch: true
  }
};

Object.entries(scenarios).forEach(([name, s]) => {
  const result = chooseDatabaseType(s);
  console.log(`${name}: ${result.type}\n  -> ${result.reason}`);
});

You should see

blog: Relational (SQL) database
  -> joins across authors, posts, comments, and tags need referential integrity and multi-table JOINs
chat: Document database (NoSQL)
  -> flexible, fast-changing message shape and high write throughput fit a schema-less document model
cache: Key-value store (e.g. Redis)
  -> sub-millisecond reads/writes on simple key lookups matter more than relationships
rag: Relational/document store + vector database (e.g. Postgres + pgvector)
  -> app data needs relations, but similarity search over embeddings needs a vector index alongside it

5-minute try-it

A fifth scenario: a multiplayer game's live leaderboard needing sub-millisecond reads of a small, frequently-updated ranked list. Add a needsRankedFastReads flag to chooseDatabaseType and decide which existing branch it should fall into, or whether it needs a new one.

One important caution

Picking a specific product (e.g. 'MongoDB') before deciding the database type the scenario actually needs — the type should drive the product choice, not the other way around.

Assuming one dominant need rules out all others, when a real system like the RAG app often needs two database types working together, not a single winner.

Pinecone: What Is a Vector Database? — How Databases Work