In database systems, normalization is the process of organizing data to reduce redundancy and improve data integrity. It involves restructuring data into multiple related tables to ensure consistency and eliminate unnecessary duplication. This approach makes databases more efficient and easier to manage.
Objectives of Normalization
Normalization achieves the following key objectives:
| Objective | Description |
|---|---|
| Minimize redundancy | Eliminate repeated data entries to save storage and improve efficiency. |
| Avoid anomalies | Prevent issues during data insertions, updates, or deletions. |
| Enhance data integrity | Ensure accurate and consistent data relationships within the database. |
Normalization in Action: A Coffee Shop Example
Imagine we run a café, and we keep track of orders using a table called orders. Below is an example of how our initial table might look:
| Order ID | Item Name | Quantity | Unit Price | Total Price |
|---|---|---|---|---|
| 1 | Brewed Coffee | 3 | 2.00 | 6.00 |
| 2 | Brewed Coffee | 2 | 2.00 | 4.00 |
| 3 | Croissant | 1 | 3.50 | 3.50 |
| 4 | Brewed Coffee | 1 | 2.00 | 2.00 |
The above table has redundancy. For example, the unit price of "Brewed Coffee" is repeated multiple times, even though it remains constant. This repetition wastes space and creates a risk of inconsistency.
Step 1: Creating a Separate Items Table
To address this, we normalize the data by creating a separate table to hold the unique details of each item, including its price. We also introduce a new column called Item ID, which acts as the primary key for this table:
| Item ID | Item Name | Unit Price |
|---|---|---|
| 1 | Brewed Coffee | 2.00 |
| 2 | Espresso | 3.00 |
| 3 | Croissant | 3.50 |
Now, instead of storing the item name and price repeatedly in the orders table, we can reference the Item ID from this new table.
Step 2: Updating the Orders Table
The updated orders table now looks like this:
| Order ID | Item ID | Quantity | Total Price |
|---|---|---|---|
| 1 | 1 | 3 | 6.00 |
| 2 | 1 | 2 | 4.00 |
| 3 | 3 | 1 | 3.50 |
| 4 | 1 | 1 | 2.00 |
In this table, the Item ID acts as a foreign key, referencing the items table. For instance, Item ID = 1 corresponds to "Brewed Coffee," which has a unit price of $2.00.
Key Benefits of Normalization
By normalizing the data:
- We reduced redundancy by storing the unit price of each item only once.
- We ensured data integrity. Any change in the price of "Brewed Coffee" needs to be made in just one place (the
itemstable). - We made the database easier to maintain and less prone to errors.
This example demonstrates how normalization creates efficient and well-structured databases by dividing data into related tables and maintaining relationships using keys.