Database Normalization

Consider a single-table design for an order system: (order_id, customer_name, customer_email, product_name, product_price, quantity). This schema has three types of anomalies that will cause data integrity problems at scale.

An update anomaly: if a customer changes their email, you must update every row containing that customer_name. Miss one row and your data is inconsistent. At 10 million orders, a partial update is almost guaranteed.

An insert anomaly: you cannot record a new product in your catalog until someone orders it: the product only exists as part of an order row.

A delete anomaly: if you delete the last order for a customer, you lose the customer's contact information entirely. There is no customer record independent of their orders.

These are not edge cases: they are structural properties of the schema. The schema's design makes correct behavior impossible to enforce at the application level without duplicating every write. Normalization eliminates these anomalies by ensuring each fact is stored in exactly one place.

Key Takeaways

• →Anomalies are structural: they cannot be fixed by application-level discipline alone
• →Update anomaly: one logical fact stored in N rows means N-1 opportunities for inconsistency
• →Insert and delete anomalies couple unrelated entities together

Operational Insights