Breaking tables into multiple tables

Breaking one large table into multiple smaller tables is a fundamental concept in database design.

It is mainly done to:

• Reduce duplication
• Improve data consistency
• Improve performance
• Make updates easier
• Avoid errors

This process is called Normalization.

1️⃣ Why Break a Table?

Imagine you have this single table:

| order_id | customer_name | customer_city | product_name | product_price | quantity |
|----------|--------------|--------------|--------------|--------------|----------|
| 1        | John Smith   | New York     | Laptop       | 1000         | 1        |
| 2        | John Smith   | New York     | Mouse        | 50           | 2        |
| 3        | Alice Brown  | Chicago      | Laptop       | 1000         | 1        |

What’s the problem?

• "John Smith" repeated
• "New York" repeated
• "Laptop" price repeated
• If price changes → must update many rows
• Risk of inconsistent data

This is inefficient and risky.

2️⃣ The Goal: Reduce Redundancy

Instead of repeating information, we separate it into logical tables.

We break the table into:

1. Customers
2. Products
3. Orders

3️⃣ Step-by-Step Table Breakdown

Customers Table

| customer_id | customer_name | customer_city |
|------------|--------------|--------------|
| 1          | John Smith   | New York     |
| 2          | Alice Brown  | Chicago      |

Products Table

| product_id | product_name | product_price |
|-----------|--------------|--------------|
| 101       | Laptop       | 1000         |
| 102       | Mouse        | 50           |

Orders Table

| order_id | customer_id | product_id | quantity |
|----------|------------|-----------|----------|
| 1        | 1          | 101       | 1        |
| 2        | 1          | 102       | 2        |
| 3        | 2          | 101       | 1        |

Now:

• No duplicated customer info
• No duplicated product info
• Price stored only once
• Updates are easy

4️⃣ What Is Normalization?

Normalization is the formal process of breaking large tables into smaller related tables.

It follows rules called Normal Forms.

The most common levels:

| Normal Form | Goal |
|------------|------|
| 1NF | Remove repeating groups |
| 2NF | Remove partial dependencies |
| 3NF | Remove transitive dependencies |

Most real-world systems use 3NF (Third Normal Form).

5️⃣ Types of Relationships

When breaking tables, relationships are created.

One-to-Many (Most Common)

One customer → Many orders

Customer (1) -------- (Many) Orders

Many-to-Many

Students and Courses

One student takes many courses

One course has many students

This requires a junction table.

Example:

Students
| student_id | name |

Courses
| course_id | course_name |

Student_Course
| student_id | course_id |

6️⃣ SQL Example: Creating Broken Tables

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    customer_city VARCHAR(100)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    product_price DECIMAL(10,2)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

7️⃣ Benefits of Breaking Tables

| Benefit | Explanation |
|----------|------------|
| Less duplication | Data stored once |
| Easier updates | Change data in one place |
| Better consistency | Avoid conflicting values |
| Smaller storage | Reduced repeated data |
| Better integrity | Enforced relationships |

8️⃣ When NOT to Break Tables Too Much

In analytics (data warehouses), we sometimes do the opposite.

We intentionally combine data into fewer tables (like star schema) for:

• Faster reporting
• Simpler queries

So:

System TypeStrategyTransactional databaseBreak tables (normalize)Data warehouseCombine tables (denormalize)

9️⃣ Before vs After Example

Before (Single Table)

| order_id | customer_name | city | product_name | price | quantity |

After (Multiple Tables)

Customers
| customer_id | name | city |

Products
| product_id | name | price |

Orders
| order_id | customer_id | product_id | quantity |

Much cleaner and safer.

🔟 Full SQL Compilation

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    customer_city VARCHAR(100)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    product_price DECIMAL(10,2)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);