Normalization by Example

Single Source

Update Once

Normalization reduces update anomalies by keeping shared facts in one place. Shipments can reference a customer row instead of copying customer details.

Program

Play the script to choose a customer and see the updated phone reused across related rows.

single_source_update.sql 
CREATE TABLE customers (customer_id INTEGER, name TEXT, phone TEXT);
CREATE TABLE shipments (shipment_id INTEGER, customer_id INTEGER, address TEXT);
INSERT INTO customers VALUES (1, 'Ada', '555-0100'), (2, 'Lin', '555-0200');
INSERT INTO shipments VALUES (10, 1, 'North'), (11, 1, 'East'), (20, 2, 'South');
UPDATE customers SET phone = '555-0199' WHERE customer_id = 1;
WITH params(wanted_customer) AS (VALUES ()) SELECT customers.name, customers.phone, shipments.address FROM shipments JOIN customers ON customers.customer_id = shipments.customer_id WHERE customers.customer_id = (SELECT wanted_customer FROM params) ORDER BY shipments.address;

CREATE TABLE customers (customer_id INTEGER, name TEXT, phone TEXT);
CREATE TABLE shipments (shipment_id INTEGER, customer_id INTEGER, address TEXT);
INSERT INTO customers VALUES (1, 'Ada', '555-0100'), (2, 'Lin', '555-0200');
INSERT INTO shipments VALUES (10, 1, 'North'), (11, 1, 'East'), (20, 2, 'South');
UPDATE customers SET phone = '555-0199' WHERE customer_id = 1;
WITH params(wanted_customer) AS (VALUES ()) SELECT customers.name, customers.phone, shipments.address FROM shipments JOIN customers ON customers.customer_id = shipments.customer_id WHERE customers.customer_id = (SELECT wanted_customer FROM params) ORDER BY shipments.address;
update anomaly Duplicated facts can become inconsistent when only one copy is updated.
single source A normalized table keeps the shared customer phone in one row.
reference Related rows store the customer key and join back to the current customer facts.