Finding relationships between tables

When working with databases, tables rarely stand alone.

They are connected through relationships.

Understanding relationships helps you:

• Combine data correctly
• Write accurate SQL queries
• Avoid duplication
• Maintain data integrity

1️⃣ What is a Table Relationship?

A relationship exists when:

One table is connected to another using a key.

This is usually done with:

• Primary Key (PK) → Unique identifier in a table
• Foreign Key (FK) → A column that references another table’s primary key

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 Customers
• That is the relationship

3️⃣ 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:

• One customer can place many orders
• Each order belongs to one customer

This is the most common relationship in databases.

2️⃣ One-to-One (1:1)

One user → One profile

User (1) -------- (1) Profile

Example:

• One employee has one ID card
• One passport belongs to one person

Less common, but useful when splitting sensitive data.

3️⃣ Many-to-Many (M:N)

Students and Courses

• One student takes many courses
• One course has many students

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:

• Unique values
• Often named: id, customer_id, product_id

2️⃣ Look for Foreign Keys

Foreign keys:

• Usually have same name as primary key in another table
• Example:
• customers.customer_id
• orders.customer_id

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:

• Data becomes inconsistent
• Reports become incorrect
• System becomes unreliable

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:

• One order to contain multiple products
• Multiple customers to buy products

🔟 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)
);