When working with databases, tables rarely stand alone.
They are connected through relationships.
Understanding relationships helps you:
1️⃣ What is a Table Relationship?
A relationship exists when:
One table is connected to another using a key.
This is usually done with:
2️⃣ Example: Customers and Orders
Customers Table
| customer_id (PK) | customer_name | city | |------------------|--------------|----------| | 1 | John Smith | New York | | 2 | Alice Brown | Chicago |
Orders Table
| order_id (PK) | customer_id (FK) | order_date | total_amount | |---------------|------------------|------------|-------------| | 101 | 1 | 2024-01-01 | 200 | | 102 | 1 | 2024-01-02 | 150 | | 103 | 2 | 2024-01-03 | 300 |
Notice:
customer_id in Orders refers to customer_id in Customers3️⃣ Types of Relationships
There are three main types.
1️⃣ One-to-Many (1:N) — Most Common
One customer → Many orders
Customer (1) -------- (Many) Orders
Example:
This is the most common relationship in databases.
2️⃣ One-to-One (1:1)
One user → One profile
User (1) -------- (1) Profile
Example:
Less common, but useful when splitting sensitive data.
3️⃣ Many-to-Many (M:N)
Students and Courses
You cannot directly connect them.
You need a junction table.
Students Table
| student_id (PK) | student_name | |-----------------|-------------| | 1 | John | | 2 | Alice |
Courses Table
| course_id (PK) | course_name | |---------------|-------------| | 101 | Math | | 102 | Science |
Enrollment Table (Junction Table)
| student_id (FK) | course_id (FK) | |----------------|---------------| | 1 | 101 | | 1 | 102 | | 2 | 101 |
This creates:
Student ↔ Enrollment ↔ Course
4️⃣ How to Find Relationships in a Database
When analyzing an existing database, you can:
1️⃣ Look for Primary Keys
Primary keys:
id, customer_id, product_id2️⃣ Look for Foreign Keys
Foreign keys:
3️⃣ Check Database Constraints (SQL)
SELECT
table_name,
column_name,
constraint_name
FROM information_schema.key_column_usage;
This shows key relationships in many SQL systems.
4️⃣ Look for Matching Data
If two tables share a column with matching values, they may be related.
Example:
Customers.customer_id Orders.customer_id
Matching values → likely relationship.
5️⃣ Using JOIN to Confirm Relationships
If tables are related, you can join them.
Example:
SELECT
c.customer_name,
o.order_date,
o.total_amount
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id;
If the join works correctly, the relationship exists.
6️⃣ Visual Representation (ERD Concept)
Relationships are often shown using an ERD (Entity Relationship Diagram).
Example structure:
Customers (1) -------- (Many) Orders Orders (Many) -------- (1) Products Students (Many) ------ (Many) Courses
7️⃣ Why Relationships Matter
| Reason | Why It Matters | |--------|---------------| | Data Integrity | Prevents invalid data | | Accurate Queries | Correct joins | | Avoid Duplication | Efficient storage | | Business Logic | Reflects real-world connections |
Without proper relationships:
8️⃣ Real-World Example (E-commerce)
Common relationships:
Customers (1) ---- (Many) Orders Orders (1) ------- (Many) Order_Items Products (1) ----- (Many) Order_Items
This structure allows:
🔟 Full SQL Example (Creating Relationships)
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100)
);
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);