Relationships

Almost every real schema is a network of tables connected by relationships, and there are exactly three shapes those relationships come in: one-to-one, one-to-many, and many-to-many. Recognizing which shape a relationship has tells you exactly how to implement it — where the foreign key goes, and whether a whole extra table is needed just to represent the relationship itself.

One-to-one

One-to-one means each row in table A corresponds to at most one row in table B, and vice versa. It's implemented by putting a foreign key in one of the two tables and adding a UNIQUE constraint on it — the UNIQUE is what turns an otherwise one-to-many foreign key into strictly one-to-one. A common real example is splitting a large, rarely-accessed set of columns off a users table into a separate profile table.

One-to-one: users and user_profiles

SQL

CREATE TABLE users (
  user_id  SERIAL PRIMARY KEY,
  email    TEXT UNIQUE NOT NULL
);

CREATE TABLE user_profiles (
  profile_id SERIAL PRIMARY KEY,
  user_id    INTEGER UNIQUE NOT NULL REFERENCES users (user_id),
  bio        TEXT,
  avatar_url TEXT
);

One-to-many

One-to-many means one row in table A can relate to many rows in table B, but each row in B relates back to only one row in A — a customer with many orders, an author with many blog posts. The foreign key lives on the "many" side, pointing back at the "one" side's primary key. This is the relationship the Foreign Keys page's orders/customers example already covers in depth.

One-to-many: customers and orders

SQL

CREATE TABLE customers (
  customer_id SERIAL PRIMARY KEY,
  name        TEXT NOT NULL
);

CREATE TABLE orders (
  order_id    SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers (customer_id),
  total_amount NUMERIC(10, 2) NOT NULL
);

Many-to-many: junction tables

Many-to-many means a row in table A can relate to many rows in table B, and a row in table B can relate to many rows in table A — a product can have many tags, and a tag can apply to many products. Neither table can hold a single foreign key to the other, because either side would need to store a variable-length list of references. The standard solution is a junction table (also called a bridge or association table) sitting between them, holding one row per pairing, with a foreign key to each side.

Many-to-many: products, tags, and a product_tags junction table

SQL

CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  name       TEXT NOT NULL
);

CREATE TABLE tags (
  tag_id SERIAL PRIMARY KEY,
  name   TEXT UNIQUE NOT NULL
);

CREATE TABLE product_tags (
  product_id INTEGER NOT NULL REFERENCES products (product_id),
  tag_id     INTEGER NOT NULL REFERENCES tags (tag_id),
  PRIMARY KEY (product_id, tag_id)
);

INSERT INTO product_tags (product_id, tag_id) VALUES (1, 3), (1, 7), (2, 3);

Querying across a junction table just means joining through it — to find every tag on a product, or every product with a given tag, both directions go through product_tags.

Querying a many-to-many relationship through the junction table

SQL

-- All tags for product 1
SELECT t.name
FROM tags t
JOIN product_tags pt ON pt.tag_id = t.tag_id
WHERE pt.product_id = 1;

-- All products tagged "sale"
SELECT p.name
FROM products p
JOIN product_tags pt ON pt.product_id = p.product_id
JOIN tags t ON t.tag_id = pt.tag_id
WHERE t.name = 'sale';

Relationship	Implementation
One-to-one	Foreign key on one side, with a UNIQUE constraint on that foreign key column
One-to-many	Plain foreign key on the "many" side, pointing at the "one" side's primary key
Many-to-many	A junction table with a foreign key to each side, usually a composite primary key on both

Junction tables can carry their own data

A junction table is not limited to just the two foreign keys — it's a real table, so it can hold extra columns describing the relationship itself. A product_tags row could add an added_at timestamp; an enrollments junction table between students and courses could carry a grade. Once the "relationship" needs its own attributes like this, a junction table is doing real work that an array or JSONB shortcut (mentioned on the Normalization page) could not cleanly replace.

One-to-one: foreign key plus UNIQUE on one of the two tables.
One-to-many: plain foreign key on the "many" side.
Many-to-many: a junction table with a foreign key to each side.
A junction table can hold its own columns once the relationship itself needs attributes.