Schema Design and Data Modeling

1Three Levels of Schema, and Why the Distinction Matters

Schema design is not "write CREATE TABLE statements." It is a three-stage process, and skipping the first two stages is exactly how you end up with a physical schema that fights the domain it's supposed to represent.

Conceptual:  Customer ---places---> Order ---contains---> Product
                 (an idea, no columns yet)

Logical:     customer(id, name, email)
             order(id, customer_id FK, placed_at)
             order_item(order_id FK, product_id FK, quantity, unit_price)
             product(id, name, price)

Physical:    CREATE TABLE customer (
               id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
               email TEXT UNIQUE NOT NULL,
               name TEXT NOT NULL
             );
             CREATE INDEX ON order_item (product_id);  -- physical-only decision

A mistake at the conceptual level (modeling "Order" and "Invoice" as the same entity when the business treats them as genuinely different lifecycles) cannot be fixed by a clever index at the physical level. Working top-down catches domain mistakes while they're still cheap — a diagram edit, not a migration.

2Entities, Attributes, and Relationships

• An entity is a distinguishable thing the business cares about tracking: Customer, Order, Product. It becomes a table.
• An attribute is a fact about an entity: Customer.email. It becomes a column.
• A relationship connects entities: "a Customer places Orders." It becomes a foreign key (for 1:N) or a junction table (for M:N) — the full mechanics are in 03-relationships/.

The recurring design question at this stage is "is this an entity, or just an attribute?"

Attribute:  Customer.shipping_address TEXT
            -- fine if a customer has exactly one address you ever need.

Entity:     Address(id, customer_id, label, street, city, is_default)
            -- necessary the moment a customer can have MULTIPLE addresses,
            -- or an address needs its own identity (used on a specific past order).

Promoting an attribute to an entity too early adds needless joins; leaving it an attribute too long forces an awkward retrofit (a comma-joined list, or repeated columns address_1, address_2) once the "exactly one" assumption breaks — the same 1NF failure mode covered in 01-normalization_and_denormalization/.

3Cardinality: What Kind of Relationship

Customer  1 ────────< N   Order            (one customer, many orders)
Order     N >──────< M    Product          (many-to-many, via order_item)
User      1 ────────1     UserProfile      (one-to-one — often just one wider table)

Cardinality determines the physical shape:

Full treatment of each shape, including when 1:1 should really be one table, is in 03-relationships/dbmodel_relationships_theory.md.

4Choosing a Primary Key: Surrogate vs. Natural

A natural key is a real-world attribute that happens to be unique (an email, a national ID, an ISBN). A surrogate key is a system-generated identifier with no business meaning (an auto-incrementing integer, a UUID).

-- Natural key: simple, but risky if the "unique" assumption is wrong
CREATE TABLE employee (
  email TEXT PRIMARY KEY,   -- what happens when two employees legitimately share an email?
  name  TEXT NOT NULL
);

-- Surrogate key: identity never changes, natural key becomes a UNIQUE constraint instead
CREATE TABLE employee (
  id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,   -- still enforced, just not load-bearing for every FK
  name  TEXT NOT NULL
);

The concrete failure mode natural keys invite: a natural key that turns out not to be as stable or unique as assumed (an email gets reused after an account is deleted; a national ID scheme changes format in another country) forces rewriting every foreign key that references it, in every child table. A surrogate key never has this problem, because it never had business meaning to begin with — it can't "turn out to be wrong" the way a business fact can.

The trade-off is a genuine one, not a free win: a surrogate key requires an extra join or lookup any time you only have the natural value on hand ("find the employee row for this email"), and it adds a column that carries no information a human can read directly. Both keys commonly coexist, exactly as above: surrogate for joins, natural under a UNIQUE constraint for lookups and real-world identity checks.

5Mapping an ER Diagram to Relational Tables — The Mechanical Rules

Once the conceptual model is settled, the logical schema is close to mechanical:

1. Every entity → one table; every attribute → one column.
2. Every 1:N relationship → a foreign key column on the "many" table, referencing the "one" table's primary key.
3. Every M:N relationship → a new junction table with a foreign key to each side; its primary key is usually the composite of both foreign keys (unless the relationship itself carries an identity, e.g. order_item.id as its own surrogate when the row needs to be referenced elsewhere, such as by a refund_line).
4. Every multi-valued attribute (an entity that can have several values of one attribute) → its own table, one row per value — this is exactly the 1NF fix from 01-normalization_and_denormalization/.

ER: Student *───enrols in───* Course   (M:N, with a grade recorded per enrolment)

Logical:
  student(id PK, name, email)
  course(code PK, title)
  enrolment(student_id FK, course_code FK, grade, PRIMARY KEY(student_id, course_code))
             ^ the relationship's own attribute (grade) lives on the junction table,
               because it describes the PAIRING, not either entity alone.

Owner 1 ──< N Pet               → pets.owner_id FK
Pet   N >──< M Vet (visits)     → visits(pet_id FK, vet_id FK, visited_at, notes)
Pet   1 ── 0..1 Microchip       → microchips.pet_id UNIQUE FK

CREATE TABLE owners (id BIGINT PRIMARY KEY, email TEXT UNIQUE NOT NULL, …);
CREATE TABLE pets   (id BIGINT PRIMARY KEY, owner_id BIGINT NOT NULL REFERENCES owners(id), name TEXT NOT NULL, species TEXT NOT NULL);
CREATE TABLE visits (pet_id BIGINT REFERENCES pets(id), vet_id BIGINT REFERENCES vets(id), visited_at TIMESTAMPTZ NOT NULL,
                     PRIMARY KEY (pet_id, vet_id, visited_at));

6Recurring Schema Design Patterns

Lookup / reference tables for a small fixed vocabulary (order status, currency codes):

CREATE TABLE order_status (code TEXT PRIMARY KEY, label TEXT NOT NULL);
INSERT INTO order_status VALUES ('pending', 'Pending'), ('shipped', 'Shipped'), ('cancelled', 'Cancelled');

CREATE TABLE "order" (
  id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status TEXT NOT NULL REFERENCES order_status(code)
);

A CHECK (status IN ('pending','shipped','cancelled')) or a native ENUM type is a lighter-weight alternative when the set almost never changes and nothing else needs to join against it (see the "normalizing lookup tables into oblivion" pitfall in 01-normalization_and_denormalization/).

Audit columns — nearly universal, cheap, and easy to forget until you need them:

created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
created_by BIGINT REFERENCES app_user(id)

Soft delete — keep the row, mark it inactive, so history and foreign keys pointing at it stay intact:

deleted_at TIMESTAMPTZ  -- NULL = active row; non-NULL = "deleted" at this instant
-- every query needs: WHERE deleted_at IS NULL
CREATE INDEX ON "order" (customer_id) WHERE deleted_at IS NULL;  -- a partial index, common pairing

Soft delete trades a hard guarantee (the row is really gone) for a query-discipline obligation (every read must remember the filter) — see Common Pitfalls.

Polymorphic association — one table that can belong to several different parent types (a Comment on either a Post or a Photo):

-- Common but structurally weak: no real foreign key is possible
CREATE TABLE comment (
  id              BIGINT PRIMARY KEY,
  commentable_type TEXT,   -- 'post' or 'photo'
  commentable_id   BIGINT, -- id in whichever table commentable_type names
  body             TEXT
);

-- Stronger alternative: one nullable FK per possible parent, with a CHECK
-- ensuring exactly one is set — the database can now actually enforce referential integrity
CREATE TABLE comment (
  id       BIGINT PRIMARY KEY,
  post_id  BIGINT REFERENCES post(id),
  photo_id BIGINT REFERENCES photo(id),
  body     TEXT,
  CHECK (num_nonnulls(post_id, photo_id) = 1)
);

The first form is popular in ORMs (Rails, Django) because it's schema-simple, but commentable_id cannot carry a real FOREIGN KEY — the database has no way to know which table to check against — so referential integrity for it lives entirely in application code.

7Schema Evolution: Designing for Change

A schema is not written once. The practical difference between a design that evolves gracefully and one that requires a rewrite:

• Additive changes are cheap: a new nullable column, a new table, a new index. These can typically be deployed without touching existing rows or blocking traffic.
• Type-narrowing or NOT-NULL-adding changes are expensive: they require validating every existing row against the new constraint, which (depending on engine and version) can mean scanning and locking the whole table.
• Renaming or repurposing a column breaks every reader that doesn't know about the change yet — the actual technique (expand/contract migrations: add the new column, dual-write, backfill, switch reads, drop the old column) belongs to migration tooling, but the schema-design implication is: prefer additive, reversible steps over a single destructive ALTER.

-- Cheap (Postgres 11+): adding a column with a constant default is metadata-only
ALTER TABLE "order" ADD COLUMN currency TEXT NOT NULL DEFAULT 'USD';

-- Expensive: this must verify EVERY existing row, and takes a stronger lock
ALTER TABLE "order" ALTER COLUMN customer_id SET NOT NULL;

Under the HoodHow it works internally▾

Surrogate keys and B-tree fan-out: a physical consequence of a logical choice

The choice of primary-key type is not just a modeling preference — it changes the physical size of every index built on top of it. A primary key is stored, verbatim, in every foreign key column that references it, and in every secondary index that includes it (directly, or via the table's clustering). A BIGINT surrogate key is 8 bytes, fixed-width. A natural key such as an ISBN-13 string or an email address is variable-width and often 15-60+ bytes.

A B-tree index page is a fixed size (8KB in Postgres, by default). More bytes per key means fewer keys fit per page, which means:

textCopy

8-byte BIGINT key:   ~500+ keys per 8KB page  ->  shallower tree, fewer page reads per lookup
40-byte TEXT key:    ~100 keys per 8KB page   ->  deeper tree, more page reads per lookup

This is why "use a surrogate integer/UUID key" is not merely a style convention — it directly bounds the fan-out (children per internal node) of every B-tree built over that key, which directly bounds tree height, which directly bounds the number of page reads a lookup or a foreign-key check costs. A wide natural key used as a PK doesn't just cost more storage; it makes every index referencing it structurally deeper. (UUIDs specifically add a second, independent cost: as random 16-byte values they defeat sequential insert locality, causing index page splits scattered across the whole tree rather than appends at the tail — a physical-layer reason many schemas prefer sequential surrogate keys, or UUIDv7's time-ordered variant, over UUIDv4.)

Column order, alignment padding, and TOAST — physical cost of "just add a column"

Postgres (like most row-store engines) lays out a row's fixed-width columns back-to-back in physical storage, and the CPU/storage layer requires certain types to start at aligned byte offsets (an 8-byte BIGINT must start at an offset divisible by 8). If a BOOLEAN (1 byte) is declared directly before a BIGINT (8 bytes), the engine inserts up to 7 bytes of invisible padding to align the following column — padding that is silently duplicated in every row. Declaring columns narrow-to-wide, or grouping same-width columns together, is a real, measurable physical-schema decision with zero effect on the logical model.

Separately, any column value large enough to make a row exceed roughly 2KB (Postgres's TOAST — The Oversized-Attribute Storage Technique — threshold, applied per-value) gets transparently moved to a side table, compressed, and replaced in the row with a small pointer. A schema with one huge TEXT/JSONB column alongside several small hot columns means every query touching only the small columns still fetches a row containing a TOAST pointer cheaply — but any query that also reads the large column pays an extra out-of-line fetch. This is a genuine argument, grounded in storage mechanics rather than taste, for splitting a rarely-read large blob column into its own 1:1 table.

Why "additive migration" is cheap and "add NOT NULL" is not — the lock and the scan

ALTER TABLE ... ADD COLUMN x TYPE DEFAULT constant in modern Postgres (11+) is O(1): the engine records the default in the table's metadata (catalog) and returns it lazily for old rows that don't physically have the new column yet — no rewrite of existing rows, and only a brief metadata-level lock (ACCESS EXCLUSIVE, but held only for the catalog update, not per-row work). ALTER TABLE ... ALTER COLUMN ... SET NOT NULL, by contrast, requires the engine to actually scan every existing row to verify none of them violate the new constraint before committing — an O(n) table scan under a lock that blocks concurrent writers for its duration. The logical-level design choice ("should this column ever be required") has a physical-level cost that scales with existing table size, which is exactly why schema-evolution strategy (add nullable, backfill in batches, then add the constraint) is a physical-layer workaround for a logical-layer decision made after data already exists.

⚠️ Common Pitfalls

1. Jumping straight to CREATE TABLE without a conceptual pass. Physical-level fixes (indexes, partitioning) cannot repair a wrong entity boundary; that requires a migration, not a tuning pass.
2. Modeling a multi-valued fact as a comma-joined column or repeated numbered columns (phone1, phone2) instead of a child table — the same 1NF violation from 01-normalization_and_denormalization/, arrived at by schema-design habit rather than by a normalization mistake.
3. Using a "natural" key that isn't actually guaranteed unique (email, before accounting for reuse after deletion; a phone number, before accounting for reassignment) — the fix cascades into every table that references it.
4. Forgetting the WHERE deleted_at IS NULL filter on soft-deleted tables — every join, every uniqueness assumption, and every count silently includes "deleted" rows unless every single query remembers the discipline; a partial UNIQUE INDEX ... WHERE deleted_at IS NULL is required if you want "no two active rows with this email" rather than "no two rows ever."
5. Choosing polymorphic association's type + id pair for correctness-critical relationships. It cannot be a real FOREIGN KEY, so an orphaned or mistyped reference is caught only if application code checks for it — and it usually doesn't, until a production incident does.
6. Adding NOT NULL or narrowing a type on a large, live table without a plan — this is an O(n) full-table validation under a blocking lock, not a metadata change, and will visibly stall production traffic if run without a maintenance window or a pre-validated CHECK (... ) NOT VALID / VALIDATE CONSTRAINT split.
7. Treating "surrogate vs natural key" as all-or-nothing. The common, correct pattern is both: a surrogate key for joins and stability, a UNIQUE constraint on the natural key for real-world identity and lookup — not picking exactly one.

📋 Quick Reference

Conceptual -> Logical -> Physical   (domain meaning -> tables/keys -> DDL/indexes/types)

Entity -> table   Attribute -> column   Relationship -> FK (1:N) or junction table (N:M)

Surrogate key:  stable identity, fixed narrow width -> shallow B-tree fan-out
Natural key:    real-world meaning, but can turn out non-unique/mutable -> cascades on fix
Common pattern: BOTH — surrogate PK + UNIQUE constraint on the natural key

Lookup table    small fixed vocabulary, needs FK/joins  -> table; else CHECK/ENUM
Audit columns   created_at, updated_at, created_by
Soft delete     deleted_at TIMESTAMPTZ, NULL = active   -> needs partial index/filter discipline
Polymorphic FK  type+id column pair = no real FK  vs  one nullable FK per parent + CHECK

Cheap migration:      ADD COLUMN ... DEFAULT const   (Postgres 11+: metadata-only)
Expensive migration:  ALTER ... SET NOT NULL / narrow a type  (full-table scan + lock)

Physical costs of a logical choice:
  wide/variable PK  -> deeper B-tree, more page reads per lookup and per FK check
  bad column order  -> alignment padding duplicated per row
  large column      -> TOAST out-of-line storage, extra fetch when read

🎤 Interview Q&A

🛠️ Practice exercises

Each exercise is a file in this topic folder. Code exercises are self-checking: run them and make the checks pass.

✅ Check yourself

Source: dbmodel_schema_design_and_data_modeling_theory.md · generated by tools/build_lessons.py