SQL Server › Normalization

What practical problems, such as update anomalies, can occur in a poorly normalized database, and how does normalization address them?

Intermediate

Answer

In a poorly normalized database, updating a single piece of information, like a customer's address stored in multiple rows across many orders, might require updating every one of those rows, and if even one is missed, the data becomes inconsistent, an issue called an update anomaly, which normalization solves by storing that address in exactly one place, referenced wherever it is needed.

-- Poorly normalized: updating a customer's address requires
-- updating it in every single order row for that customer
UPDATE Orders SET CustomerAddress = 'New Address' WHERE CustomerId = 123;
-- Properly normalized: update happens in exactly one place
UPDATE Customers SET Address = 'New Address' WHERE CustomerId = 123;

Real-world example A support team discovers a customer's shipping address is inconsistent across several of their past orders due to a poorly normalized design, prompting the team to redesign the schema so address information is stored and updated in exactly one place.

Common follow-ups: What other types of anomalies besides update anomalies can occur in poorly normalized databases?;How do you identify these kinds of anomalies in an existing schema before they cause real problems?

Data Types & Schema Design;Constraints (Primary Key Foreign Key Check & Unique)