🧩Schema design and relationships

Invariants live in keys and constraints

Strong schemas encode cardinality (1:N, M:N via junction), ownership of lifecycle (who deletes first), and where uniqueness prevents double charges or duplicate enrollments. System-design answers should tie tables to concrete queries, SLOs, and failure domains, not only ER boxes.

SQL

Many-to-many resolution table

CREATE TABLE study_investigator (
  study_id BIGINT NOT NULL REFERENCES studies(study_id),
  investigator_id BIGINT NOT NULL REFERENCES investigators(investigator_id),
  role TEXT NOT NULL,
  PRIMARY KEY (study_id, investigator_id)
);

✓Illustrative result set

result
CREATE TABLE

🔗Relationship patterns

Pick the shape, then name the hot queries.

Relationship	SQL pattern
1:N	FK on the many side toward the one
M:N	Junction table + composite PK / UNIQUE
1:1	UNIQUE FK or shared PK when profiles are truly one row each

Diagram-only design

Beautiful ERDs with no mention of read/write QPS or p95 targets.
Multi-tenant data without tenant_id discipline or RLS story.
Backup, failover, and replica lag omitted from the reliability sketch.

Access patterns + isolation

Walk through three to five critical queries and their expected cardinalities.
Call out hot keys, counters, and how you will shard or queue them.
Describe zero-downtime migrations and how deploys stay compatible across versions.

Normalize first

Preserve write integrity with FKs and narrow rows; denormalize only where measured reads justify duplication.

Interview signal

Mention indexes, replication tier, and consistency for user-visible reads, not only table names.

Evolution

Treat schema like an API: additive migrations, backfills with dual writes, and feature flags beat “big bang” table rewrites under traffic.

TL;DR

In system design, the schema is part of the service contract: tables encode invariants (keys, types, constraints) that every app version must respect. Normalize OLTP for integrity; denormalize selectively for read paths with clear ownership. Couple schema changes with migration tooling, compatibility windows, and metrics, breaking the database breaks every client at once.