Now that you understand OLTP systems handle daily operations, let's dive deeper into how to design these databases properly.
Poor database design causes major headaches: duplicate data, inconsistent information, and wasted storage space.
Engagement Message
Name one problem that could arise if customer addresses were stored in multiple places?
Here's a common problem: imagine storing customer orders where each order row contains the customer's name, address, and phone number.
When a customer moves, you'd need to update their address in every single order record!
Engagement Message
What's one drawback of having to update every order record when a customer moves?
This duplication problem is called "data redundancy." It wastes space and creates consistency nightmares when information needs updating.
The solution? Normalization - a process that eliminates redundancy by organizing data into separate, related tables.
Engagement Message
How do separate tables help eliminate redundancy?
In a normalized design, you'd have a Customers table with one row per customer, and an Orders table that references the customer by ID.
Now when Mrs. Johnson moves, you update her address once in the Customers table!
Engagement Message
How many places would you need to update her address now?
Let's see how normalization works in practice.
Here is an example of a table that is not normalized:
Emma's address is repeated in every order.
Normalizing the tables, by separating the data, solves this issue:
