In this lesson, you will learn how to organize your data into tables efficiently to prevent errors and make your database easier to manage.
Organizing for Integrity
When we design a database, our primary goal is to ensure that each piece of information is stored in exactly one place. If we store the same data in multiple locations—a problem known as data redundancy—we risk "update anomalies." For example, if you store a customer's address in both an Orders table and a Customers table, and the customer moves, you might update one record but forget the other. Suddenly, your database contradicts itself.
To fix this, we use a process called normalization. This involves breaking large, cluttered tables into smaller, focused ones based on the entities they represent.
A Practical Example
Imagine you have a single table that tracks Orders and includes CustomerName and CustomerEmail in every row.
Bad Design (Redundant):
| OrderID | Item | CustomerName | CustomerEmail |
|---------|---------|--------------|--------------------|
| 101 | Laptop | Alice Smith | alice@example.com |
| 102 | Mouse | Alice Smith | alice@example.com |
If Alice changes her email, you have to find every order she ever placed to update it. If you miss one, the data is corrupted.
Good Design (Normalized):
We split this into two tables: Customers and Orders.
-- Customers Table
| CustomerID | Name | Email |
|------------|-------------|-------------------|
| 1 | Alice Smith | alice@example.com |
-- Orders Table
| OrderID | Item | CustomerID |
|---------|--------|------------|
| 101 | Laptop | 1 |
| 102 | Mouse | 1 |
By using the CustomerID as a reference, Alice’s information lives in only one place. Now, if her email changes, you update one row in the Customers table, and every order is automatically "linked" to the correct, updated information. This reduces storage waste and ensures that your data remains a single source of truth.
Key Takeaway: Design your schema by isolating entities into separate tables and linking them with keys; this minimizes redundancy and ensures that updates to your data happen in only one place.