Relational foundations

The animation below shows the failure this design invites. Dana changes her email address, so the application issues an update against her first order, row 101. Before you press play, decide what you expect the table to say afterward when it is asked for Dana’s email. As you step through it, watch row 103, which still holds a copy of the old address. Then check the table’s answer against your prediction.

The update did exactly what it was asked to do. It changed the one row that matched its condition and reported success, while the second copy of Dana’s email sat outside the condition and kept the old value. The table now holds two different answers to the same question, and this failure is called an update anomaly.

The same design produces two related failures. A new customer cannot be recorded until they place an order, because customer information only lives inside order rows, and deleting a customer’s last order erases everything the table knew about them. Database texts call these the insertion anomaly and the deletion anomaly, and all three grow from the same root.

You can confirm the update anomaly directly by asking the corrupted table for Dana’s email.

select distinct email
from orders
where customer = 'Dana';

The query returns two rows where there should be one. No error was raised at any point, the update reported the expected row count, and the inconsistency will now sit in the table until someone happens to notice it.

Look again at what each column of orders describes. The item and amount columns state facts about the order itself, which is exactly what the row’s key identifies. The email column is different, because an email address is a fact about the customer rather than about any single order. Storing it on every order row is what created the copies, and the copies are what allowed one update to split the truth.

This relationship between columns is called functional dependency, and it says that a column’s value is determined by something. Dana’s email is determined by the customer, while the amount is determined by the order. A table is soundly designed when every non-key column is determined by that table’s key. William Kent, an early researcher of the relational model, condensed this into a summary that database courses still teach. A non-key column should state a fact about the key, the whole key, and nothing but the key.

After the split, each fact has exactly one home. Higher forms exist beyond these three, and Boyce-Codd normal form in particular tightens an edge case involving overlapping composite keys, but third normal form is the working standard this guide holds you to.

Normalization carries a cost of its own, because every table you split off becomes a join that future queries have to pay for. Weighing that cost honestly is the judgment this guide exists to teach. An anomaly is a correctness bug that silently corrupts recorded history, while an extra join is a performance cost that can usually be bought back with an index or a cache. Speed can be recovered after the fact, but a history that was recorded wrong often cannot be.

The default therefore follows the write path. In a system that accepts writes, stay at third normal form unless you can name the specific dependency you are breaking and point to the measurement that justifies breaking it.

Analytical models are the deliberate exception to that default. A star schema flattens customer facts into one wide dimension table, because a warehouse is read-heavy and is rebuilt from source data, which moves the anomaly risk out of the table and into the pipeline that loads it. That is the same trade evaluated under different costs, and this track covers it in the fact vs dimension guide.

• Over-splitting into name-value rows. A generic attributes table of name-value pairs technically passes every normal form. In exchange it defeats column types, constraints, and the query optimizer.
• Normalizing a reporting model out of habit. Snowflaking a warehouse dimension reintroduces joins that the read path pays for on every query. The write-path default does not transfer to a reporting model.
• Trusting a uniqueness you never declared. The split only protects you if customers.customer_id is a key the database enforces. Declare the constraint, or the duplicates you removed will eventually find their way back in.
• Storing derived values. An order total stored beside its line items is a copy of a computation, and copies drift apart over time. Either derive the total when it is needed, or treat the stored copy as a cache and give it a rebuild story.