One fact, one place: update, insert and delete anomalies

A table that looks convenient

Imagine the shop kept its sales in one wide table, the way a spreadsheet would: one row per product sold, with everything you might want to show next to it.

CREATE TEMP TABLE sales_sheet AS
SELECT o.id AS order_id, o.ordered_at,
       c.name AS customer_name, c.email AS customer_email, c.city AS customer_city,
       p.name AS product_name, p.category, oi.quantity, oi.unit_price
FROM orders o
JOIN customers c    ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id = oi.product_id;

No joins needed to show anything. But look at Asha:

 order_id | customer_name |  customer_email  |        product_name
----------+---------------+------------------+-----------------------------
      101 | Asha Rao      | asha@example.com | Mechanical keyboard
      101 | Asha Rao      | asha@example.com | Wireless mouse
      103 | Asha Rao      | asha@example.com | Laptop stand
      103 | Asha Rao      | asha@example.com | USB-C hub
      103 | Asha Rao      | asha@example.com | Wireless mouse
      108 | Asha Rao      | asha@example.com | Noise-cancelling headphones

Her name and email are stored six times. Each copy is a chance to disagree with the others. We ran all three classic failures on this table.

Update anomaly

Asha changes her email. A support tool updates it on her latest order:

UPDATE sales_sheet SET customer_email = 'asha.rao@example.com' WHERE order_id = 108;

Now the table says Asha has two emails:

 customer_name |                 emails
---------------+-----------------------------------------
 Asha Rao      | {asha.rao@example.com,asha@example.com}

Which is right? Nothing in the data says. Every program that changes a repeated fact must find and change every copy, and one that misses some creates contradictions.

Delete anomaly

Order 107 is the only order containing the Portable SSD. Delete the order, for example because it was a test, and:

DELETE FROM sales_sheet WHERE order_id = 107;   -- DELETE 2
SELECT count(*) FROM sales_sheet WHERE product_name = 'Portable SSD 1TB';   -- 0

The shop has forgotten the product exists, along with its category. Deleting a fact about one thing (an order) destroyed facts about another (a product).

Insert anomaly

The shop starts selling a monitor arm. In a table where every row is an order line, order_id must have a value:

ERROR:  null value in column "order_id" of relation "sales_sheet" violates not-null constraint
DETAIL:  Failing row contains (null, null, null, null, null, Monitor arm, Accessories, null, 3499.00).

You cannot record a product until someone buys it. Allow the NULL instead, and you get “order lines” that are not order lines.

Functional dependencies: the tool for finding them

To spot this systematically, ask of every column: what determines its value?

• customer_email is determined by the customer, not by the order line.
• category and the list price are determined by the product.
• quantity and unit_price are determined by the order line: this order, this product.

“The product determines its category” is a functional dependency, written product → category. Whenever a column depends on something smaller than the row’s key, that column belongs in another table, keyed by what it depends on. The next page turns that rule into the normal forms.

Check yourself

1. What causes an update anomaly?

   Show answer

   The same fact stored in several rows. Changing some copies but not all leaves the data contradicting itself.

   I could explain this without looking

2. Why did deleting order 107 lose a product?

   Show answer

   The product’s name and category were stored only in order-line rows. When the last order containing it was deleted, nothing else recorded that the product exists.

   I could explain this without looking

3. In sales_sheet, what determines customer_city, and what does that tell you?

   Show answer

   The customer determines it, not the order line. It depends on only part of what identifies a row, so it belongs in a customers table keyed by the customer.

   I could explain this without looking