Data Modeling in System Design

Data modeling is deciding what entities exist, how they are identified, and how they connect. Tables, keys, relationships. The bar is a schema that serves the APIs — not 3NF theater. Postgres is the default.

This note follows Evan's walkthrough. The statement model is Core SQL Concepts. Access patterns come from API Design in System Design. The denormalized copy lives in Caching in System Design. Tenant rows are Building a Multi-Tenant Backend with Hono, Better Auth, Drizzle, and Postgres RLS. This note is the schema.

Pattern Map

1. Why the model exists

The schema is the API the database will enforce. Draw it next to the database box, not as a separate interview.

• It shows up twice. Early: the nouns — auction, item, bid, user. Later, while serving each endpoint: columns, keys, indexes that make that GET cheap.
• A reasonable schema sets up reads, writes, consistency, and growth. A sloppy one forces the rest of the design to apologize.
• You are not in a data-engineering loop. Name the entities, the identifiers, and the queries. Then move.

Failure: spending the hour on 3NF proofs, or skipping the schema so sharding and caching have nothing to stand on.

2. Pick the store

Postgres is the production default. The others earn their keep.

APIs
  → Postgres → Redis cache     (default)
  → Document                   (evolving schema)
  → Wide-column                (append-heavy)
  → Graph                      (almost never)

• Relational. Tables, foreign keys, ACID. Users, posts, likes. JOIN is how "posts by people I follow" is a query, not a product. Strong consistency — payments, inventory, a unique email — is why this stack stays on Postgres. Scale is replicas, pooling, caching, then sharding — not a different database.
• Document. JSON blobs, flexible fields. Embed posts inside the user so one load returns both. Joins are weaker, so you denormalize. The usual reason is an evolving schema. A scoped design has already frozen the requirements. Unless the interviewer names rapid field change, skip it.
• Key-value. Exact match. user:123, feed:123. Fast, blind. You duplicate values across keys because you cannot join. Redis in front of Postgres is the production use. DynamoDB is a key-value store with document features — still not the default source of truth here.
• Wide-column. Rows with different columns, append-heavy. Telemetry, IoT, time-series. A queue and a batch into Postgres often beat reaching for Cassandra in an interview.
• Graph. Nodes and edges. A social network sounds like one. Facebook still models that graph in MySQL. Reaching for Neo4j because the problem has a follow edge is a junior tell.

Failure: picking Mongo or a graph store to look sophisticated, then needing joins and a unique constraint the store will not enforce.

3. Three drivers

Volume, access patterns, consistency. Everything after this section is a tool for these three.

• Volume decides where rows can live. Millions of users may split user data and post data across stores. The schemas then have to name how they point at each other.
• Access patterns are the important one. They come from the APIs. GET /users/{id}/posts is an index or a denormalized list. Ask what query each endpoint runs.
• Consistency decides how tightly coupled the data can be. A charge stays in one ACID database. A like on a feed can land a second later in Redis.

Failure: a schema that ignores how GET /users/{id}/posts actually queries, then wondering why the join is the outage.

4. Entities, keys, relationships

System-generated ids. Foreign keys that make cardinality obvious. Domain names, not "Entity A."

organizations: id (PK), name
users:         id (PK), email UNIQUE
memberships:   organization_id (FK), user_id (FK)   -- PK (organization_id, user_id)
invoices:      id (PK), organization_id (FK), created_at

users ──────────┐
                ├── memberships
organizations ──┤
                └── invoices

• A primary key uniquely identifies the row. Use id, not email — business data changes. A foreign key is a field that points at another table's PK. invoices.organization_id → organizations.id is how a tenant owns a row.
• Write the FKs. One user, many memberships is then obvious. A likes table with user_id and post_id is many-to-many without saying the words. Reciting 1:N vs N:M is how candidates stall.
• One-to-one is rare. Two tables that always load together are often one table.
• This stack puts organization_id on every tenant-owned row. Isolation that lives only in Hono is one forgotten filter away from a leak.

Failure: email as the primary key, then a user changes address and every child row is orphaned — or a sequential integer guessed across tenants and called access control.

5. Constraints

The schema is the API. Application WHERE clauses are a habit, not a guarantee.

• NOT NULL, UNIQUE, CHECK — email is unique, qty is positive, status is one of a known set. Postgres refuses the bad row. Hono never sees it.
• Foreign keys enforce referential integrity: no invoice for an organization that does not exist. They cost a lookup on write. At huge write scale some shops drop them and enforce in the app. Name that trade-off. Do not skip FKs by default.
• Drizzle can type the column. It cannot replace a constraint the database does not have.

Failure: uniqueness only in the Hono handler. Two concurrent POSTs both read "free," both insert. The unique email is now two rows.

6. Normalization

Store each fact in one place. Duplicate only for a named read that indexes cannot save.

• Normalized: user data lives in users. Posts hold user_id. A rename is one UPDATE. A join loads the name with the post.
• Denormalized: username copied onto every post. The feed has no join. A rename is every post the user ever wrote. Miss one, and the system lies.
• Start normalized. Index first. If the read is still the incident — a feed, a dashboard aggregate — denormalize. Prefer the copy in Redis so Postgres stays clean. Event logs and audit snapshots are the other honest exception: they capture a point in time on purpose.
• The statement model, and when a join is the product, is Core SQL Concepts.

Failure: username on every post, then a rename that rewrites the table — or denormalizing first because "joins are slow," with no query that needed it.

7. Indexes

An index makes a lookup cheap and a write a little more expensive. Work backwards from the APIs.

• GET /users/{id}/posts needs an index on posts.user_id. Sorted by recency: composite (user_id, created_at). Comments for a post: comments.post_id. Do not index a column because it exists.
• A B-Tree is the default. Equality and a range ride it. A sequential scan that was instant on a laptop is the missing index in production.
• Too many indexes slow every INSERT. Cover the endpoints you named. Leave the rest until EXPLAIN asks.

Failure: no index on the foreign key the list endpoint filters, then paging with OFFSET over a sequential scan. The API was fine. The access path was not.

8. Sharding

Shard only after the data no longer fits one node. The key is usually permanent. The full map is Sharding in System Design.

• Shard by the primary access pattern so related rows land together. Posts by post_id, comments on the same shard as their post — a post and its comments is one database, not a scatter-gather.
• Hash the key for even spread. Time-range sharding for a write-heavy table puts every insert on today's shard. That is a hot shard. Time-range belongs on archival and analytics, not the OLTP write path.
• Cross-shard joins are the cost. A timeline of followed users, sharded by user_id, queries many nodes and merges. Cache the merge, or pick a different key, or accept the fan-out. Do not discover this after the data is split.
• Partitioning inside one Postgres is not sharding. One primary, one WAL. The difference is Core SQL Concepts and the database Q&A.

Failure: sharding by created_at so all writes hit today's shard, or sharding posts and comments on different keys so every detail page is a cross-shard join.

Recap Q&A