Relationships

Introduction

Relational databases get their name from relationships between tables. The three patterns — 1:1, 1:N, M:N — describe almost every real-world connection.

Understanding the topic

Core concepts to understand:

• 1:1 — one user has one profile.
• 1:N — one user has many orders.
• M:N — one student has many courses; one course has many students.
• M:N requires a join table (also called bridge or pivot).
• Choose the right relationship by the real-world cardinality.

Syntax reference

Visual workflow / architecture:

1:1   users ───── profiles  (profile.user_id UNIQUE FK)

1:N   users ──────▶ orders     (orders.user_id FK)

M:N   students ──▶ enrolments ◀── courses
                   (student_id, course_id) composite PK

Real-world use

Every CRM, LMS, e-commerce, social network and SaaS platform is a graph of these three relationships.

Best practices

• Model relationships explicitly — never embed lists in a single column.
• Always use a join table for M:N.
• Index the FK columns on the join table.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Examples of each relationship type.
• Q2. How to model M:N.
• Q3. Why never store a CSV list of IDs in one column?
• Q4. What columns belong on a join table?
• Q5. Difference between 1:1 and 1:N at schema level.