Database Normalization: A Simple Story of Café Delight

Imagine you are opening a new café called Café Delight. You want to use a database to track your orders, customers, and menu items. When you first design the database, it’s a bit chaotic. Let’s see how normalization can help us organize things.

1NF (First Normal Form): “No Messy Tables Allowed”

The Problem: You start with a table that looks like this:

| OrderID | CustomerName | MenuItems              | Quantities |
|---------|--------------|------------------------|------------|
| 1       | Alice        | Coffee, Croissant      | 1, 2       |
| 2       | Bob          | Sandwich, Orange Juice | 1, 1       |

• The MenuItems and Quantities columns have multiple values (e.g., "Coffee, Croissant" and "1, 2").
• This format is messy and hard to query.

The Rule: In 1NF, each column must contain atomic (indivisible) values.

Solution: Split the table so that each piece of data is in its own row:

| OrderID | CustomerName | MenuItem      | Quantity |
|---------|--------------|---------------|----------|
| 1       | Alice        | Coffee        | 1        |
| 1       | Alice        | Croissant     | 2        |
| 2       | Bob          | Sandwich      | 1        |
| 2       | Bob          | Orange Juice  | 1        |

Now each column contains only one value, and the table is in 1NF.

2NF (Second Normal Form): “No Partial Dependencies”

The Problem: Your new table is much better, but it still has a problem. Let’s say you extend it to include the price of each menu item:

| OrderID | CustomerName | MenuItem      | Quantity | Price  |
|---------|--------------|---------------|----------|--------|
| 1       | Alice        | Coffee        | 1        | $3.00  |
| 1       | Alice        | Croissant     | 2        | $2.50  |
| 2       | Bob          | Sandwich      | 1        | $5.00  |
| 2       | Bob          | Orange Juice  | 1        | $4.00  |

Here’s the issue:

• The Price of a menu item depends on the MenuItem, not the OrderID.
• If you update the price of “Coffee,” you’d need to update it in multiple rows, increasing the chance of errors.

The Rule: In 2NF, all non-key attributes must depend on the entire primary key. (No partial dependencies!)

Solution: Break the table into two tables:

1. An `Orders` table that tracks orders and quantities:

| OrderID | CustomerName | MenuItem      | Quantity |
|---------|--------------|---------------|----------|
| 1       | Alice        | Coffee        | 1        |
| 1       | Alice        | Croissant     | 2        |
| 2       | Bob          | Sandwich      | 1        |
| 2       | Bob          | Orange Juice  | 1        |

2. A `Menu` table that tracks menu items and their prices:

| MenuItem      | Price  |
|---------------|--------|
| Coffee        | $3.00  |
| Croissant     | $2.50  |
| Sandwich      | $5.00  |
| Orange Juice  | $4.00  |

Now, the Price depends only on the MenuItem, and the table is in 2NF.

3NF (Third Normal Form): “No Transitive Dependencies”

The Problem: Your database is getting better, but there’s still room for improvement. Let’s say you add a column to track each customer’s phone number:

| OrderID | CustomerName | MenuItem      | Quantity | PhoneNumber  |
|---------|--------------|---------------|----------|--------------|
| 1       | Alice        | Coffee        | 1        | 123-456-7890 |
| 1       | Alice        | Croissant     | 2        | 123-456-7890 |
| 2       | Bob          | Sandwich      | 1        | 987-654-3210 |
| 2       | Bob          | Orange Juice  | 1        | 987-654-3210 |

Here’s the issue:

• The PhoneNumber depends on the CustomerName, not directly on the OrderID.
• If Alice changes her phone number, you’d need to update it in multiple rows.

The Rule: In 3NF, there should be no transitive dependencies. (A non-key column should not depend on another non-key column.)

Solution: Split the table into three tables:

1. An `Orders` table:

| OrderID | CustomerName | MenuItem      | Quantity |
|---------|--------------|---------------|----------|
| 1       | Alice        | Coffee        | 1        |
| 1       | Alice        | Croissant     | 2        |
| 2       | Bob          | Sandwich      | 1        |
| 2       | Bob          | Orange Juice  | 1        |


2. A `Menu` table (unchanged):

| MenuItem      | Price  |
|---------------|--------|
| Coffee        | $3.00  |
| Croissant     | $2.50  |
| Sandwich      | $5.00  |
| Orange Juice  | $4.00  |


3. A `Customers` table:

| CustomerName | PhoneNumber  |
|--------------|--------------|
| Alice        | 123-456-7890 |
| Bob          | 987-654-3210 |

Now, the PhoneNumber depends only on the CustomerName, and the table is in 3NF.

Conclusion

Through Normalization:

• 1NF ensures that data is organized into atomic values (no repeating groups).
• 2NF eliminates partial dependencies by splitting data into smaller, logically related tables.
• 3NF removes transitive dependencies, ensuring every non-key column depends only on the primary key.

With a properly normalized database, Café Delight can now handle updates, queries, and maintenance efficiently without redundant data.