PostgreSQL Relationships

In a relational database like PostgreSQL, data is distributed across multiple tables. To join these datasets, we establish database relationships.

There are three core types of relationships: One-to-One, One-to-Many, and Many-to-Many. Let's review their implementations.

---

Core Specifications

• Why it exists: Relational structures exist to divide data logically into structured tables, enforcing normalization constraints to eliminate redundant database states.
• What problem it solves: It prevents data synchronization bugs (e.g. updating a customer's email in one record but missing it in duplicate columns elsewhere) and keeps query execution pathways optimized.
• How it works internally: PostgreSQL uses Foreign Key constraints to link rows across tables. It enforces relationships via pointer checks: 1:1 relationships require a UNIQUE foreign key, 1:N relationships use standard indexes on foreign keys, and N:M relationships use a dedicated junction table with composite keys.
• When to use it: Use relational systems when structural data consistency, acid transaction guarantees, and complex multi-table joins (such as tags, purchase invoices, and user security scopes) are required.
• How it is used in real projects: Production software separates authentication databases from user metadata using a 1:1 table link, references invoice details to customers via a 1:N link, and categorizes products with tags via an N:M junction schema to enable multi-filter search operations.

---

One-to-One Relationships (1:1)

Mental Model: Passport & Citizen

A citizen can only have one passport, and a passport belongs to exactly one citizen.

Codebase Setup

In your database migration scripts directory (e.g., database/migrations/), define the tables where the foreign key is set to UNIQUE.

Step 1: Create the Parent Table

-- filepath: database/migrations/01_create_citizens.sql
-- Purpose: Store core citizen profile data.

CREATE TABLE citizens (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create the Child Table with UNIQUE Foreign Key

-- filepath: database/migrations/02_create_passports.sql
-- Purpose: Store passport data, linked uniquely to a citizen.

CREATE TABLE passports (
    id SERIAL PRIMARY KEY,
    passport_number VARCHAR(20) UNIQUE NOT NULL,
    citizen_id INT UNIQUE, -- UNIQUE constraint makes this a 1:1 relationship
    FOREIGN KEY (citizen_id) REFERENCES citizens(id) ON DELETE CASCADE
);

---

One-to-Many Relationships (1:N)

Mental Model: Mother & Children

A mother can have multiple children, but each child has exactly one biological mother.

Codebase Setup

The foreign key resides on the "Many" side (the child table).

Step 1: Create the Parent Table

-- filepath: database/migrations/03_create_mothers.sql
-- Purpose: Store mother records.

CREATE TABLE mothers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

Step 2: Create the Child Table

-- filepath: database/migrations/04_create_children.sql
-- Purpose: Store child records linked to a mother.

CREATE TABLE children (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    mother_id INT, -- No unique constraint, multiple children can point to the same mother
    FOREIGN KEY (mother_id) REFERENCES mothers(id) ON DELETE SET NULL
);

Step 3: Add an Index on the Foreign Key

-- filepath: database/migrations/05_index_children.sql
-- Purpose: Optimize relationship joins and lookup times.

CREATE INDEX idx_children_mother_id ON children(mother_id);

---

Many-to-Many Relationships (N:M)

Mental Model: Students & Classes

A student can register for multiple classes, and a class can have multiple students.

Codebase Setup

To link two tables in a many-to-many relationship, we use a Junction Table (or Join Table) in the middle.

Step 1: Create the Two Core Entity Tables

-- filepath: database/migrations/06_create_students_and_classes.sql
-- Purpose: Store independent student and class records.

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE classes (
    id SERIAL PRIMARY KEY,
    subject_name VARCHAR(100) NOT NULL
);

Step 2: Create the Junction Table in the Middle

We use a Composite Primary Key (student_id, class_id) to prevent a student from enrolling in the same class twice.

-- filepath: database/migrations/07_create_enrollments_junction.sql
-- Purpose: Link students and classes together.

CREATE TABLE enrollments (
    student_id INT REFERENCES students(id) ON DELETE CASCADE,
    class_id INT REFERENCES classes(id) ON DELETE CASCADE,
    enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (student_id, class_id) -- Prevents duplicate links
);

Step 3: Index the Junction Table for Reverse Lookups

-- filepath: database/migrations/08_index_enrollments_reverse.sql
-- Purpose: Optimize class-to-student relationship lookups.

CREATE INDEX idx_enrollments_class_id ON enrollments(class_id);

---

Practical Project Use Cases

1. Separation of User Login vs Profile Meta (1:1)

In high-traffic applications (like SaaS platforms), checking user authentication tokens on every click requires querying user credentials.

• Real-World Example: We separate critical login fields (email, hashedPassword) from heavy configuration metadata (theme preference, bio, avatar) into two tables. This keeps login table rows narrow, speeding up authentication checks:

-- filepath: database/migrations/09_split_user_profiles.sql
-- Purpose: Split profile details into User and UserSettings for rapid Auth queries.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL
);

CREATE TABLE user_settings (
    user_id INT UNIQUE PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    theme VARCHAR(20) DEFAULT 'dark',
    notifications_enabled BOOLEAN DEFAULT TRUE,
    avatar_url TEXT
);

2. Multi-Tag Blog Index Filtering (N:M)

In content-management systems or e-commerce products, fetching records matching specific categories.

• Real-World Example: We query articles that overlap with a list of tags using a SQL INNER JOIN over our junction table:

-- filepath: database/queries/fetch_articles_by_tags.sql
-- Purpose: Fetch articles containing the tags 'NextJS' and 'ORM'.

SELECT a.id, a.title, a.published_at
FROM articles a
INNER JOIN article_tags_junction j ON a.id = j.article_id
INNER JOIN tags t ON t.id = j.tag_id
WHERE t.name IN ('NextJS', 'ORM')
GROUP BY a.id, a.title, a.published_at
HAVING COUNT(DISTINCT t.name) = 2;

---

Common Pitfalls

1. Putting the Foreign Key on the wrong side

If mapping a User having many Orders, placing the order_id in the users table means a user can never have more than one order.

• Fix: The Foreign Key always goes on the "Many" side. So user_id must reside inside the orders table.

2. Cascading deletes without caution

Setting ON DELETE CASCADE deletes related data automatically. If a user account is deleted, all their orders are wiped out.

• Fix: Use ON DELETE SET NULL or restrict the delete if active orders exist, preventing accidental data loss.

---

Interview Questions

What is the difference between a Foreign Key and a Primary Key?

A Primary Key uniquely identifies a specific row within its own table and cannot contain NULL values. A Foreign Key is a column in a table that references the Primary Key of another table, establishing a link between the datasets. Foreign keys can contain NULL values (unless configured as NOT NULL) and do not need to be unique.

---

Summary

• 1:1: Citizen -> Passport (Foreign Key has a UNIQUE constraint).
• 1:N: Mother -> Children (Foreign Key resides on the "Many" side).
• N:M: Students -> Classes (Requires a middle junction table).