From entities to tables

Every design has data, and the shape you give it decides which queries are easy, which are slow and which bugs are impossible. In an interview, the "core entities" step and the data model are where you show that. This lesson covers the relational vocabulary; the next ones cover other kinds of databases and how to choose.

Entities, attributes and keys

An entity is a thing your system keeps track of: a User, a Link, an Event, a Seat. Each becomes a table (or a collection), each instance a row, and its attributes become columns.

Every row needs a primary key that identifies it forever. Prefer a surrogate key with no business meaning: an auto-increment integer, a UUID, or a time-ordered ID like a ULID or Snowflake ID. Natural keys like email addresses change, and when they do, everything pointing at them must change too.

1 create table users (
2   id          bigint primary key,
3   email       text not null unique,
4   name        text not null,
5   created_at  timestamptz not null default now()
6 );

The unique constraint on email still enforces what the business cares about, while the id stays stable.

Relationships

Entities relate to each other in three ways, and each has a standard shape.

Foreign keys can be enforced by the database, which rejects a post whose author doesn't exist. At very large scale some teams drop enforcement for speed or because the tables live on different shards, and check in application code instead. Say which you'd do and why.

Normalization in one paragraph

Normalization means storing each fact exactly once. A post doesn't copy its author's name; it stores author_id and joins to users when it needs the name. Then renaming a user is one update, and the data can never disagree with itself. Most designs should start normalized.

Denormalization copies data on purpose so reads avoid joins or extra lookups: a post row that stores author_name, a counter column like_count instead of counting rows. It speeds up hot reads and costs you consistency work on every write. Do it for the specific queries that need it, and say how the copy stays correct: in the same transaction, by an asynchronous job, or by accepting that it may be briefly stale.

Modeling state, not just data

Many entities move through states: an order is pending, paid, shipped; a seat is available, held, sold. Model the state explicitly as a column with a fixed set of values, plus the timestamps that matter (held_until, paid_at). Then rules like "only available seats can be held" become simple conditional updates the database can enforce, which the concurrency lessons build on.

Time and IDs

Store timestamps in UTC with a timezone-aware type. For IDs on distributed systems, time-ordered IDs (Snowflake, ULID, UUIDv7) are popular: generated without coordination, roughly sortable by creation time, and friendly to B-tree indexes because new rows land at the end.