Database Model Options

Picking a database is really picking a data model: tables, documents, key-value pairs, wide rows or graphs. Each one makes some queries cheap and others painful. In most designs a relational database is the right default. The other models are tools you add for a specific access pattern.

Quick comparison#

Five data models cover almost every design.

Relational databases (SQL)#

Data lives in tables with a fixed schema defined up front. Each row is a record and each column has a strict type. A primary key identifies a row; a foreign key references a row in another table. Normalization splits data into separate tables so facts aren't repeated.

Relational databases provide ACID transactions:

• Atomicity: all of a transaction happens or none of it does.
• Consistency: constraints hold before and after every transaction.
• Isolation: concurrent transactions don't see each other's partial changes.
• Durability: once committed, data survives crashes.

Scaling a relational database#

When to choose SQL#

1. Money or mission-critical state. Payments, banking, and inventory (don't oversell). Balances must be correct immediately, which needs atomicity and isolation.
2. Highly relational data. "Comments on posts by users who are friends with X" is one SQL query.
3. A predictable schema. You know what a User or Product looks like.
4. Complex filtering. Search by price, date, location and color at once.
5. Integrity rules. A Ticket can't exist without its Event (foreign keys).
6. Multi-row transactions. One action must update several tables or none.

Handling heavy traffic on SQL usually goes in layers:

1. Indexing and connection pooling.
2. Read replicas.
3. A cache like Redis for hot queries.
4. Application-level sharding on a stable key like user_id.

At high scale, expensive joins get replaced by denormalized columns or cached precomputed results.

Document databases#

Records are JSON-like documents (JSON or BSON). The database doesn't require a fixed schema, so two documents in the same collection can have different fields. Modeling shifts from normalizing (splitting into tables) to embedding (nesting related data in one document). Think of a scrapbook where everything about a person is glued onto one page, versus a filing cabinet with a drawer per kind of record.

When it fits:

• Deeply nested data that would need many joins in SQL.
• Polymorphic data. In a product catalog, laptops have CPU speeds while shirts have sizes and fabrics.
• Rapidly changing models, where you'd rather not run ALTER TABLE migrations every week.
• Heterogeneous profiles. Some users have long work histories and links, others almost nothing. One document per user avoids a wide table full of NULLs.

Risks: data gets duplicated to keep reads fast (a username change may touch many documents), and many document stores only guarantee atomicity within a single document.

Why it's less common in interviews: the requirements are fixed for the 45 minutes, so the biggest advantage, schema flexibility, rarely comes into play. If the data is well defined and relational, the usual reasoning is to stay with PostgreSQL for joins and ACID.

Key-value stores#

A key-value store is a giant dictionary: you GET and SET a value by its unique key. Think of a wall of P.O. boxes.

You must know your box number (the key), and you can't ask "give me all mail from people named John". Lookups are extremely fast and scale horizontally, but there are no ad-hoc queries or joins.

In practice you rarely choose key-value instead of SQL. A common hybrid: PostgreSQL is the source of truth and Redis is the speed layer, using cache-aside:

1. Check Redis, and return on a hit.
2. On a miss, read PostgreSQL.
3. Store the result in Redis with a TTL, then return it.

See Caching.

Because there are no joins, you denormalize and store one entry per access path:

The risk is consistency: change the name and every copy must be updated. DynamoDB-style stores also suit flat, high-volume lookups by a single id, such as session management or feature flags.

Wide-column databases#

Rows live in partitions, and each row can have many, varying columns. These stores are built for massive write volume across many commodity servers. Picture a ledger per user: every new event is appended to the end of that user's ledger, so thousands of users can write at once without waiting on each other.

Two keys matter:

1. Partition key decides which node stores the data (e.g. sensor_id, chat_id). Everything for one key lives together.
2. Clustering key orders rows inside a partition (e.g. timestamp), which makes range scans like "the last 24 hours" fast.

Pros: peer-to-peer clusters with no single point of failure (Cassandra), fast writes, and a natural fit for time series.

Cons: you must know your queries up front, and a new query often means a new, duplicated table. Background compaction can cause CPU and disk spikes.

Typical uses: millions of IoT readings per second (partition by sensor_id, cluster by timestamp) and chat history at very large scale (partition by chat_id, so a conversation is stored together).

Graph databases#

Data is nodes (users, products) and edges (follows, bought, friends-with). Graph databases are optimized for multi-hop traversals that would need repeated self-joins in SQL. Finding friends of friends means following edges rather than looking things up.

They're hard to scale horizontally, and many large social networks don't use one as primary storage. Facebook, for example, built TAO, a graph-aware caching layer on top of MySQL. For "design a social network", the usual answer is a relational database plus a cache of adjacency lists.

Mention a graph database only when deep traversal is the core problem:

• Fraud detection, e.g. rings of accounts sharing phone numbers, devices and payment methods.
• Recommendations ("customers who bought this also bought...").
• Network impact analysis, e.g. how a failure at node A affects node Z in a power grid.

How to choose#

A simple path works for most designs.

1. Start with PostgreSQL for core entities. It handles relationships and consistency.
2. If reads are too slow, add Redis in front.
3. If one table's write volume outgrows a single leader (logs, events, metrics), move that table to Cassandra or a similar store.
4. If the schema is truly unpredictable or polymorphic, consider a document store.
5. Avoid graph databases unless multi-hop traversal is the heart of the problem.

Key takeaways#

• Relational is the default: joins, constraints and ACID transactions for core data.
• Document stores trade joins and cross-document transactions for flexible, nested records.
• Key-value stores are for fast lookups by key, usually as a cache in front of a source of truth.
• Wide-column stores handle huge write volumes when you design tables around known queries.
• Graph databases solve deep traversal problems but are hard to scale. Use them sparingly.