The Three Relationship Shapes

Why "what shape is this relationship" matters

Module 2 introduced foreign keys as the way one table points at another. What it didn't say is that the shape of the relationship between those two tables determines exactly where the foreign key column should go and whether you might need a third table that nobody mentioned yet.

Every relationship between two tables falls into one of three shapes. Once you can recognize which shape you're looking at, the foreign key's position stops being a guess. It becomes mechanical.

One-to-many — the most common shape

A one-to-many relationship means: one row in Table A is connected to many rows in Table B, but each row in Table B is only connected to one row in Table A.

A user has many posts. Each post belongs to exactly one user. That's one-to-many. A customer has many orders, each order belongs to one customer. That's one-to-many. A pet has many vet visits, each visit belongs to one pet. That's one-to-many.

Once you start naming these out loud, it becomes obvious that one-to-many is everywhere. It's the default shape relational databases were built around.

Where the foreign key goes

The foreign key lives on the "many" side. Always. Every row on the many side carries a pointer to its one row on the other side.

CREATE TABLE users (
  id    UUID PRIMARY KEY,
  name  TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL
);

CREATE TABLE posts (
  id        UUID PRIMARY KEY,
  user_id   UUID REFERENCES users(id),   -- the FK lives here, on the "many" side
  title     TEXT NOT NULL,
  body      TEXT
);

Every post row has a user_id pointing at exactly one user. The users table has no idea how many posts each user has — that's discoverable by querying the posts table, not by storing it on users.

Many-to-many — when both sides have many

A many-to-many relationship means: one row in Table A connects to many rows in Table B, and one row in Table B connects to many rows in Table A.

A book has many authors, an author writes many books. That's many-to-many. A student takes many classes, a class has many students. Many-to-many. A recipe has many ingredients, an ingredient appears in many recipes. Many-to-many.

Where the foreign key goes — the join table trick

Many-to-many can't be expressed with one foreign key column on either side. Both sides have "many," and a column can only hold one value per row. So relational databases solve it with a third table whose entire job is to record the connections.

CREATE TABLE books (
  id    UUID PRIMARY KEY,
  title TEXT NOT NULL
);

CREATE TABLE authors (
  id    UUID PRIMARY KEY,
  name  TEXT NOT NULL
);

-- The join table — its only job is to connect the other two.
CREATE TABLE book_authors (
  book_id   UUID REFERENCES books(id),
  author_id UUID REFERENCES authors(id),
  PRIMARY KEY (book_id, author_id)
);

The book_authors table is a join table (sometimes called a junction table or pivot table). Each row in it represents one connection: "this book has this author." A book with three authors creates three rows in book_authors. An author who wrote ten books creates ten rows.

The foreign keys live on the join table, not on either of the two main tables. Books doesn't know who its authors are directly. Authors doesn't know what books they wrote directly. Both discoveries happen by joining through the junction table.

One-to-one — the rarest shape

A one-to-one relationship means: one row in Table A connects to exactly one row in Table B, and vice versa. Neither side has many.

This is the rarest of the three because most "one-to-one" relationships could just be a single table with more columns. If a user has exactly one profile, why not put the profile fields directly on the user row?

When one-to-one is actually the right answer

A few honest reasons to split a one-to-one across two tables:

• Separate concerns. A users table for login info (email, password hash) and a profiles table for display info (bio, avatar). Different systems touch each one. Splitting them keeps the auth layer simple.
• Optional data. Every user has an account, but only some users complete their profile. The profiles row only exists when there's something to store.
• Performance, occasionally. A users row that's read on every request shouldn't carry a giant bio field that's only needed sometimes. Splitting keeps the hot row small.

Where the foreign key goes

Either side can hold the FK. The convention is to put it on the table that's "secondary" or "optional" — the profile points at the user, not the other way around — and to add a UNIQUE constraint on the FK column to enforce that the relationship really is one-to-one.

CREATE TABLE users (
  id    UUID PRIMARY KEY,
  email TEXT UNIQUE NOT NULL
);

CREATE TABLE profiles (
  id        UUID PRIMARY KEY,
  user_id   UUID UNIQUE REFERENCES users(id),  -- UNIQUE is what makes it 1:1
  bio       TEXT,
  avatar_url TEXT
);

Without the UNIQUE on user_id, this would technically be a one-to-many: nothing would stop two profile rows from pointing at the same user. The UNIQUE constraint is what enforces the "one-to-one" promise at the database level.

The shape decides the layout

Every relational schema in every app you've ever used is some combination of these three shapes. There isn't a fourth. Once you can look at two nouns in a product and confidently say which shape connects them, you've cleared the biggest design hurdle in relational data.

The next page goes deeper on foreign keys themselves — what they actually are under the hood, and the surprising fact that they don't always have to point at a primary key.