Chapter 5 — Relationships, Joins & References

The why

The real world is relational. A user has orders. An order has items. An item belongs to a product. A product belongs to a category. If your database can't model these connections cleanly, your application code becomes a tangle of manual lookups, duplicate data, and impossible-to-debug consistency bugs.

Relationships are the hardest part of database design, full stop. Get them right and queries are clean, fast, and correct. Get them wrong and you're writing 7 queries where 1 should suffice, or worse — returning wrong data that nobody catches because "it happens intermittently."

Core concepts

One-to-many (1:N)

The most common relationship. The "many" side holds the foreign key.

-- One user → many orders
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);
 
CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),  -- FK on the "many" side
  total NUMERIC(10,

Rule: The FK always goes on the "many" side. orders.user_id references users.id. Never the other way — you can't predict how many order_ids to add to the users table.

One-to-one (1:1)

Rare but useful. The FK goes on either table — but you add a UNIQUE constraint so each row references only one parent row.

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL
);
 
CREATE TABLE user_profiles (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL UNIQUE REFERENCES users(id),  -- UNIQUE makes it 1:1
  bio TEXT,
  avatar_url VARCHAR(500)

When to use 1:1:

• Splitting a wide table for performance (frequently accessed columns vs rarely accessed ones)
• Optional data that's only present for some users (premium features, settings)
• Sensitive data isolated in a separate table with tighter access control

When NOT to use 1:1: If the columns are always accessed together, keep them in one table. A 1:1 JOIN is overhead you don't need.

Many-to-many (M:N)

Requires a junction table (also called a join table, bridge table, or associative entity). Students ↔ Courses:

CREATE TABLE students (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);
 
CREATE TABLE courses (
  id BIGSERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL
);
 
-- Junction table
CREATE TABLE enrollments (
  student_id BIGINT

JOIN types — the definitive guide

SQL has 5 JOIN types. Memorize what each one returns:

-- Sample data:
-- users: (1, Alice), (2, Bob), (3, Carol)
-- orders: (user_id=1, total=50), (user_id=1, total=75), (user_id=NULL, total=10)

When to use each:

• INNER JOIN — default. "Show me users who have placed orders."
• LEFT JOIN — 90% of your JOINs. "Show me all users, with their orders if they have any."
• RIGHT JOIN — almost never. Rewrite as a LEFT JOIN (swap table order). More readable.
• FULL OUTER JOIN — data integrity checks. "Find orders with invalid user_ids AND users with no orders."
• CROSS JOIN — generate test data or all combinations. Rare in production queries.

-- These are identical:
SELECT * FROM orders RIGHT JOIN users ON orders.user_id = users.id;
SELECT * FROM users LEFT JOIN orders 

The N+1 query problem

The single most common backend performance bug. You've probably written it without knowing.

// ❌ N+1 — 1 query for users + N queries for their orders
const users = await db.query('SELECT * FROM users');      // 1 query
for (const user of users) {
  user.orders = await db.query(                            

Where N+1 hides:

• ORMs (Prisma, Sequelize, ActiveRecord) with lazy loading
• GraphQL resolvers (each field triggers its own DB call)
• REST endpoints that return nested resources
• Any loop that calls a database function

How to spot N+1: Look for database queries inside loops. If you see for (const x of array) { await query(...) }, you have an N+1 problem. The fix is always the same: batch the query — JOIN, WHERE IN, or a data loader.

// ✅ Fix with WHERE IN
const users = await db.query('SELECT * FROM users');
const userIds = users.map(u => u.id);
const orders = await db.

MongoDB relationships: embed vs reference revisited

In MongoDB, you model relationships differently:

Real-world example: Instagram's data model

Instagram's core data model is a masterclass in relationship design:

1. User ↔ Post (1:N) — Each post has user_id. Classic FK on the "many" side.
2. User ↔ User (M:N via "follows") — A follows table with follower_id and followee_id. Both reference users.id. This is a self-referential M:N — the same table on both sides of the junction.
3. Post ↔ Hashtag (M:N) — A post_hashtags junction table. Instagram denormalized popular hashtag counts onto the hashtag table itself (hashtags.post_count) because displaying trending hashtags was a hot query.
4. User ↔ Post (M:N via "likes") — A likes table with user_id and post_id. Instagram shards this table by post_id because likes are always fetched in the context of a post ("who liked post X?"), rarely in the context of a user ("what did user X like?").

Instagram's lesson: M:N junction tables become real entities over time. A likes table starts as (user_id, post_id) but evolves to include created_at, then becomes the backbone of the recommendation algorithm.

Common mistakes

Production notes

• Junction tables can become massive. Instagram's likes table has trillions of rows. When junction tables grow, partition them (by post_id range or by date) and consider sharding.
• Avoid CASCADE deletes on large tables. Deleting a user with 500,000 orders via ON DELETE CASCADE locks every one of those order rows in a single transaction. Use a background job for bulk cleanup instead.
• json_agg and array_agg are your friends. PostgreSQL can return nested JSON directly, avoiding N+1 in APIs. Use with LEFT JOIN + GROUP BY for efficient nested responses.

Performance notes

• JOIN order matters. PostgreSQL's planner picks the optimal order automatically — but only with accurate statistics. Run ANALYZE regularly.
• Hash JOIN vs Nested Loop vs Merge JOIN. PostgreSQL chooses automatically. Nested loop is fast for small tables, hash join for large ones. If the planner picks wrong, check your statistics (ANALYZE).
• WHERE IN with large arrays is slow. More than ~1000 values in ANY($1) can degrade. For bulk lookups, use a temporary table or split into batches.

Security notes

• Foreign keys enforce referential integrity. An attacker can't insert order.user_id = 99999 if user 99999 doesn't exist. This is a security feature, not just a data feature.
• FKs prevent orphaned data. Without FKs, a buggy delete can leave order_items referencing deleted orders, which then show up in reports with null data — misleading, hard to detect, and sometimes a compliance violation.

Exercises

Beginner

1. Create three tables: authors, books, and a junction table book_authors (a book can have multiple authors). Insert data. Write queries to get all books by a given author and all authors of a given book.
2. Write a LEFT JOIN query that returns all users and their orders (if any). Then write an INNER JOIN query. Explain the difference in the result set.

Intermediate

1. Simulate the N+1 problem: write a script that fetches 50 users, then fetches each user's orders in a loop. Measure the time. Then refactor to use a single JOIN query. Measure the time difference.
2. Design a self-referential relationship (e.g., employees with a manager_id that references the same employees table). Write a recursive CTE that returns the entire management chain for a given employee.

Advanced

1. Design a "friendship" M:N model where friendship is bidirectional (if A is friends with B, B is friends with A). Write the schema, insert constraints, and write a query to find mutual friends of two users.
2. Implement a generic tagging system using PostgreSQL's ltree extension or a polymorphic junction table. Explain the tradeoffs between a single tags junction table vs per-entity junction tables (post_tags, product_tags).

Interview questions

Beginner

1. What is a foreign key? A column (or set of columns) in one table that references the primary key of another table, enforcing referential integrity — you can't insert a child row referencing a parent that doesn't exist.

2. What's the difference between LEFT JOIN and INNER JOIN? INNER JOIN returns only rows where there's a match in both tables. LEFT JOIN returns all rows from the left table plus matching rows from the right table, filling unmatched columns with NULL.

3. What is the N+1 query problem? Executing 1 query to fetch N records, then N additional queries (one per record) to fetch related data — resulting in N+1 total queries instead of 1 well-constructed JOIN or batch query.

Senior

4. How would you model a M:N relationship with additional attributes on the relationship? Use a junction table with columns beyond just the two foreign keys. Example: enrollments(student_id, course_id, enrolled_at, grade, completed). The junction becomes a first-class entity.

5. When would you denormalize a relationship, embedding child data into the parent row? When the child data is always accessed with the parent, rarely changes independently, and is bounded in size. Example: shipping address on an order — once the order ships, the address should never change. Storing it on the order is a snapshot of the truth at the time of the order. Also common in event sourcing: store the full state with the event, not just a foreign key.

6. How do you handle cascading deletes in a large production database? Avoid ON DELETE CASCADE for large child tables — it runs in a single transaction that locks all affected rows. Instead: use a soft delete (deleted_at), have a background job process deletes in batches, or use ON DELETE SET NULL for non-critical relationships and clean up orphans later.

Summary

Relationships are the backbone of database design. One-to-one is rare. One-to-many is the default — FK always on the "many" side. Many-to-many requires a junction table — treat it as a real entity, not just a connection. LEFT JOIN is your workhorse; INNER JOIN when you only want matches. The N+1 problem is everywhere — spot it by looking for queries inside loops, and fix it with JOINs or batch queries with WHERE IN.

Quick recall

• 1:N → FK on the "many" side. 1:1 → FK + UNIQUE. M:N → junction table.
• LEFT JOIN by default. INNER JOIN when you want only matching rows. Never RIGHT JOIN.
• N+1: 1 query + N queries in a loop. Fix: JOIN or WHERE IN.
• Index every FK. Choose ON DELETE behavior explicitly.
• Junction tables are first-class entities — add timestamps, metadata, and their own columns.
• MongoDB: embed small/bounded children, reference large/unbounded children.

Quiz

1. Where does the FK go in a 1:N relationship? On the "many" side — orders.user_id REFERENCES users(id).
2. What does a junction table do? Implements M:N relationships by storing pairs of foreign keys, optionally with its own attributes (timestamps, metadata).
3. How do you fix the N+1 problem? Replace the loop of individual queries with a JOIN (one query) or batch query with WHERE IN (two queries: parents + children, joined in code).
4. What does ON DELETE CASCADE do? Automatically deletes child rows when the parent row is deleted. Dangerous on large tables — can cause long-running transactions.
5. Why shouldn't you use RIGHT JOIN? It's semantically identical to LEFT JOIN with the table order swapped, but LEFT JOIN is universally understood and expected by every developer.
6. When would you store the same data twice (denormalize a relationship)? When the query pattern requires it AND it's faster than a JOIN, AND you have a plan for consistency. Example: storing product_name on order_items for historical accuracy.
7. What's a self-referential FK? A FK that references the same table — e.g., employees.manager_id REFERENCES employees(id). Enables hierarchical data (org charts, comment threads, category trees).