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.

wanted_customer
single_source_update.sql
Replay: real traced execution (multi-file project)
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 (1)) 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 (2)) 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;

  1. tables ← 1 row

    1CREATE TABLE customers (customer_id INTEGER, name TEXT, phone TEXT);2CREATE TABLE shipments (shipment_id INTEGER, customer_id INTEGER, address TEXT);
    values this step1 rowtables

  2. tables ← 2 rows

    1CREATE TABLE customers (customer_id INTEGER, name TEXT, phone TEXT);2CREATE TABLE shipments (shipment_id INTEGER, customer_id INTEGER, address TEXT);3INSERT INTO customers VALUES (1, 'Ada', '555-0100'), (2, 'Lin', '555-0200');
    values this step2 rowstables

  3. customers ← 2 rows

    2CREATE TABLE shipments (shipment_id INTEGER, customer_id INTEGER, address TEXT);3INSERT INTO customers VALUES (1, 'Ada', '555-0100'), (2, 'Lin', '555-0200');4INSERT INTO shipments VALUES (10, 1, 'North'), (11, 1, 'East'), (20, 2, 'South');
    values this step2 rowscustomers

  4. shipments ← 3 rows

    3INSERT INTO customers VALUES (1, 'Ada', '555-0100'), (2, 'Lin', '555-0200');4INSERT INTO shipments VALUES (10, 1, 'North'), (11, 1, 'East'), (20, 2, 'South');5UPDATE customers SET phone = '555-0199' WHERE customer_id = 1;
    values this step3 rowsshipments

  5. customers ← 2 rows

    4INSERT INTO shipments VALUES (10, 1, 'North'), (11, 1, 'East'), (20, 2, 'South');5UPDATE customers SET phone = '555-0199' WHERE customer_id = 1;6WITH params(wanted_customer) AS (VALUES (1)) 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;
    values this step2 rowscustomers

  6. result ← 2 rows

    5UPDATE customers SET phone = '555-0199' WHERE customer_id = 1;6WITH params(wanted_customer) AS (VALUES (1)) 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;
    values this step2 rowsresult

  1. tables ← 1 row

    1CREATE TABLE customers (customer_id INTEGER, name TEXT, phone TEXT);2CREATE TABLE shipments (shipment_id INTEGER, customer_id INTEGER, address TEXT);
    values this step1 rowtables

  2. tables ← 2 rows

    1CREATE TABLE customers (customer_id INTEGER, name TEXT, phone TEXT);2CREATE TABLE shipments (shipment_id INTEGER, customer_id INTEGER, address TEXT);3INSERT INTO customers VALUES (1, 'Ada', '555-0100'), (2, 'Lin', '555-0200');
    values this step2 rowstables

  3. customers ← 2 rows

    2CREATE TABLE shipments (shipment_id INTEGER, customer_id INTEGER, address TEXT);3INSERT INTO customers VALUES (1, 'Ada', '555-0100'), (2, 'Lin', '555-0200');4INSERT INTO shipments VALUES (10, 1, 'North'), (11, 1, 'East'), (20, 2, 'South');
    values this step2 rowscustomers

  4. shipments ← 3 rows

    3INSERT INTO customers VALUES (1, 'Ada', '555-0100'), (2, 'Lin', '555-0200');4INSERT INTO shipments VALUES (10, 1, 'North'), (11, 1, 'East'), (20, 2, 'South');5UPDATE customers SET phone = '555-0199' WHERE customer_id = 1;
    values this step3 rowsshipments

  5. customers ← 2 rows

    4INSERT INTO shipments VALUES (10, 1, 'North'), (11, 1, 'East'), (20, 2, 'South');5UPDATE customers SET phone = '555-0199' WHERE customer_id = 1;6WITH params(wanted_customer) AS (VALUES (2)) 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;
    values this step2 rowscustomers

  6. result ← 1 row

    5UPDATE customers SET phone = '555-0199' WHERE customer_id = 1;6WITH params(wanted_customer) AS (VALUES (2)) 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;
    values this step1 rowresult
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.