Every duplicate customer record, every orphaned order, every report where the numbers don't add up traces back to one moment: the day someone designed the schema. In this lesson you step behind the SELECT statement and learn how tables are supposed to relate to each other — so you can design schemas that stay consistent, and read someone else's schema like a map instead of a maze.
What you'll learn
What
Database design is the practice of deciding which tables exist, which columns they hold, and how rows in one table connect to rows in another. The core tools are keys — a primary key uniquely identifies each row, a foreign key points from one table to a row in another — and relationships: one-to-one, one-to-many, and many-to-many.
Why
A schema is a contract. If orders.customer_id must match a real row in customers, the database can enforce that for you — no orphaned orders, ever. Without keys and relationships, every analyst query becomes an act of faith, and every join is a guess about which columns line up.
Where it's used
Every relational system you will ever query — the OLTP (Online Transaction Processing) database behind an app, and the analytics warehouse you run reports against — is built from these concepts. Data scientists read ER diagrams weekly and design tables whenever they build a feature store or a reporting mart.
Where this runs in production
Shopify's core commerce schema — shops, products, variants, orders, line_items — is a textbook set of one-to-many relationships held together by foreign keys. Every order line points at exactly one order and one product variant.
A booking connects one guest to one listing for a date range. Reviews, payments, and messages all hang off the booking's primary key, so analysts can join the entire trip story from a single id.
Every Stripe charge references a customer and a payment method by key. Foreign key discipline is why a charge can never point at a customer that does not exist — a property you badly want when the rows represent dollars.
A primary key is a column (or combination of columns) whose value uniquely identifies each row in a table. It must be unique and it must never be NULL. In PostgreSQL you usually declare it as id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY — a surrogate key the database generates for you, with no business meaning at all.
Two customers can both be named 'Maria Garcia', so a name is a terrible identifier. A passport number identifies exactly one person, forever, even if she changes her name. That's the difference between a natural attribute and a key. The analogy's limit: passports expire and get reissued — a good primary key never changes for the life of the row.
A foreign key is a column in one table that stores the primary key of a row in another table. orders.customer_id is a foreign key into customers.id. Declaring it with REFERENCES customers(id) makes the database enforce referential integrity: you cannot insert an order for a customer that does not exist, and you cannot delete a customer who still has orders (unless you say what should happen with ON DELETE).
SQL has no direct way to store a many-to-many relationship. You resolve it with a third table — called a junction, bridge, or associative table — holding one row per pairing: enrollments(student_id, course_id). Each column is a foreign key, and together they usually form a composite primary key (a primary key made of more than one column).
An entity-relationship (ER) diagram draws each table as a box and each relationship as a line, often annotated with 'crow's feet' showing the many side. Like a subway map, it deliberately hides detail — data types, indexes — so the shape of the system is visible at a glance. When you join a new team, ask for the ER diagram before you write a single query.
CREATE TABLE customers ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email TEXT NOT NULL UNIQUE, full_name TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE TABLE orders ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, customer_id BIGINT NOT NULL REFERENCES customers(id), status TEXT NOT NULL DEFAULT 'pending', ordered_at TIMESTAMPTZ NOT NULL DEFAULT now() );
This is the canonical one-to-many pattern in PostgreSQL. The FK lives on orders (the many side). NOT NULL on customer_id means every order must belong to someone; drop the NOT NULL and you allow 'anonymous' orders — a deliberate design decision, not an accident.
Columnar warehouses like BigQuery, Redshift, and Snowflake either do not enforce foreign keys or treat them as documentation only. The relationships still exist logically — a star schema's fact table still points at dimension tables — but nothing stops a bad load from inserting orphans. In a warehouse, referential integrity becomes a data-quality test you run (with dbt or similar), not a constraint the engine enforces. Design as if the keys were enforced; verify because they aren't.
Click each table to see its keys. Follow the edges: every arrow is a foreign key pointing from the 'many' side to the 'one' side.
A clinic needs a patients table where every patient can be identified unambiguously.
GENERATED ALWAYS AS IDENTITY tells PostgreSQL to hand out the next integer automatically — you never insert it yourself. PRIMARY KEY bundles two constraints: UNIQUE and NOT NULL. Two patients can share a name and a birthday; they can never share an id.
Your task
A streaming service needs three tables: users, playlists, and a junction table connecting playlists to tracks (a playlist holds many tracks; a track appears on many playlists). Fill in the blanks so all keys and relationships are declared correctly, then run the verification query at the bottom.
name | track_count -------+------------- Focus | 2 (1 row)
Write your solution in the editor on the right, then hit Run.
Which two constraints does PRIMARY KEY combine?
In a one-to-many relationship between customers and orders, where does the foreign key live?
Why do most production schemas use a surrogate key (generated id) instead of a natural key like email?
What is the correct way to model this?
A fintech app needs an accounts table (id, owner_email) and a transfers table where each transfer references two accounts: the sender and the receiver. Write both CREATE TABLE statements. transfers needs: id (PK), from_account and to_account (both NOT NULL FKs to accounts), and amount_cents BIGINT NOT NULL.
What does this situation demonstrate?
What is the difference between a primary key and a foreign key?
A primary key uniquely identifies each row within its own table — it must be unique and non-NULL, and there is exactly one per table. A foreign key is a column in one table that stores the primary key of a row in another table, creating a relationship between them. The primary key answers 'which row is this?'; the foreign key answers 'which row over there does this row belong to?'. Declaring the foreign key with REFERENCES makes the database enforce referential integrity, so you can never point at a row that does not exist. A table has one primary key but can hold many foreign keys — an orders table might reference customers, warehouses, and promotions at once. In practice I default to surrogate primary keys plus UNIQUE constraints on natural candidates, so the identifiers that foreign keys depend on never change.
How would you model a many-to-many relationship, and where do attributes of the relationship go?
SQL cannot store a many-to-many directly, so you introduce a junction table with one row per pairing — for students and courses, that's enrollments(student_id, course_id). Each column is a foreign key into its parent table, and together they usually form a composite primary key, which also prevents duplicate pairings for free. Attributes that describe the relationship itself — enrollment date, grade, role — belong on the junction table, because they don't describe the student alone or the course alone but the combination. Querying is always a two-hop join through the bridge. When I see a comma-separated list of ids stuffed into a text column, that's the anti-pattern this design exists to prevent: it breaks foreign key checks, indexing, and joins simultaneously.
Why do analytics warehouses often not enforce foreign keys, and how does that change how you work?
OLTP databases enforce foreign keys because they take writes row by row and correctness per transaction is the whole point. Warehouses like Redshift, Snowflake, and BigQuery ingest millions of rows in bulk, and checking every row against parent tables during a load would be prohibitively slow — so they either ignore FK declarations or keep them as metadata hints for the optimizer and BI tools. The relationships still exist logically: a star schema's fact table conceptually references every dimension. What changes is where integrity lives — it moves from a constraint the engine enforces to a test the team runs, typically a LEFT JOIN checking for orphaned keys after each load, automated in something like dbt. So I design the warehouse as if keys were enforced, and then I verify with tests, because nothing else will.
Common Mistakes to Avoid
1) Choosing a mutable natural key (email, username, phone) as the primary key — when it changes, every referencing row breaks; use a surrogate id and a UNIQUE constraint instead. 2) Putting the foreign key on the 'one' side of a one-to-many (order_id on customers), which silently caps the relationship at one row. 3) Modeling many-to-many with a comma-separated id list in a TEXT column — no FK enforcement, no indexes, unjoinable. 4) Slapping ON DELETE CASCADE everywhere: deleting one parent can silently wipe thousands of child rows you needed for auditing. 5) Assuming declared foreign keys are enforced in the warehouse — Redshift/Snowflake/BigQuery treat them as documentation, so orphan checks belong in your pipeline tests.
Ask the AI Tutor
Try these prompts in the AI Tutor panel: • 'ELI5: what's the difference between a primary key and a foreign key?' • 'Give me a business domain and make me identify every relationship as 1:1, 1:N, or M:N.' • 'Show me a schema with a design flaw and let me find it.' • 'Explain when I'd pick ON DELETE CASCADE vs SET NULL vs the default, with examples.' • 'Interview mode: quiz me on junction tables and composite keys, then grade my answers.'
Glossary
Schema — the set of tables, columns, keys, and constraints that define a database's structure. Primary key (PK) — the column(s) that uniquely identify each row; UNIQUE + NOT NULL. Surrogate key — a generated, meaningless identifier (an identity/serial id). Natural key — a real-world attribute that happens to be unique, like an ISO country code. Foreign key (FK) — a column holding another table's primary key, declared with REFERENCES. Referential integrity — the guarantee that every foreign key value points at an existing parent row. One-to-many — one parent row relates to many child rows; the FK lives on the child. Many-to-many — both sides relate to many; requires a junction table. Junction table — the bridge table (also: associative or bridge table) resolving a many-to-many. Composite key — a primary key made of two or more columns. ER diagram — entity-relationship diagram; boxes for tables, lines for relationships. Star schema — a warehouse layout with a central fact table referencing surrounding dimension tables.
Recommended Resources
• Docs: PostgreSQL manual, 'Constraints' chapter — primary keys, foreign keys, and every ON DELETE option from the source. • Read: 'The Data Warehouse Toolkit' by Ralph Kimball, chapter 1, for where star schemas come from. • Tool: dbdiagram.io lets you sketch ER diagrams from a few lines of text — draw the e-commerce schema from this lesson in five minutes. • Practice: open any app you use daily and sketch its probable schema — entities, keys, relationships. • Next in DSM: you can now connect tables correctly — Normalization teaches you how to split them correctly.
Recap
✓ A schema is tables plus enforced rules — keys and constraints — not just tables. ✓ A primary key uniquely identifies a row: UNIQUE + NOT NULL, one per table, ideally a stable surrogate id. ✓ A foreign key stores a parent row's primary key; REFERENCES makes the database enforce referential integrity. ✓ In one-to-many, the FK lives on the many side; many-to-many needs a junction table with a composite key. ✓ ON DELETE behavior (NO ACTION, SET NULL, CASCADE) is a design decision about what happens to children when a parent dies. ✓ Analytics warehouses declare but rarely enforce FKs — integrity there is a test you run, not a constraint the engine applies. ✓ ER diagrams and star schemas are the maps you read before querying any unfamiliar database. Next up: Normalization. You know how tables connect — next you'll learn the rules (1NF through 3NF) for deciding what belongs in each table, and when analytics work justifies breaking those rules on purpose.
Run your code to see the output here.