Store each fact only once: Free Lesson

Two emails for one customer

A customer, Ewa, writes in. She changed her email last month, but the shop's receipts still go to her old one.

You look in the database. Her email is typed on every order she ever made. Someone updated one order and missed the others. Which email is right? The database cannot tell you.

By the end of this lesson

Note

I can split data into tables so each fact is stored once.

Key words

Word	What it means
Duplication	the same fact stored in more than one place
Anomaly	data that disagrees with itself
Normalise	to split tables so each fact is stored once
Schema	the plan of tables and columns
NoSQL	databases that do not store data in related tables
Document	one record stored whole, with its parts inside it

The cells students fill in are left blank in this preview.

A fact stored twice can disagree

In the shop's first design, each order holds the customer's name and email. Ewa has two orders, so her email is stored twice.

When it changes, every copy must change. Miss one and the data disagrees with itself. This is an anomaly. It is not a typing error. It is a design that makes errors easy.

Try it: update one copy

Code: sql

-- Ewa changes her email. Support updates one of her orders.
UPDATE orders SET email = '[email protected]' WHERE id = 103;

-- Now the database holds two different emails for one person.
SELECT id, customer, email FROM orders WHERE customer = 'Ewa Nowak';

Split the table so each fact lives once

Ask of each column: what is this a fact about? A name and an email are facts about a customer. A total is a fact about an order.

So give customers their own table, and let each order point at its customer with a foreign key. Now the email is stored once. One update fixes it everywhere. This is called normalising.

Worked example: what each fact is about

Column	A fact about	Goes in table
customer name	the customer	customers
email	the customer	customers
total	the order	orders
customer_id	which customer made the order	orders

The cells students fill in are left blank in this preview.

Try it: update once, see it everywhere

Code: sql

-- The email is stored once, so one update is enough.
UPDATE customers SET email = '[email protected]' WHERE id = 3;

-- Every one of Ewa's orders now shows the new email.
SELECT orders.id, customers.name, customers.email
FROM orders
JOIN customers ON orders.customer_id = customers.id;

Normalising has a cost

More tables mean more joins. A report that once read one table may now join four. That is usually fine, and indexes on the keys keep it fast.

Sometimes a value is copied on purpose. The price a customer paid is stored on the order line. The product's price will change next month, but what they paid must not.

A common mistake

Warning

Many people think any value stored twice is a design error. A copy is wrong when it should always match the original. It is right when it records a moment, such as the price on a receipt.

Where a NoSQL database fits

Not every system uses related tables. A document database, one kind of NoSQL, stores each record whole. An order and its lines sit together in one document.

It fits when data is always read as a whole, its shape changes often, or it is very large. The cost is the old problem: a fact copied into many documents must be kept in step by your code.

Question	Related tables	Documents
Is each fact stored once?	Yes, by design	Often copied
Can you ask new questions easily?	Yes, with joins	Harder
Does a changing shape need a new schema?	Yes	No
Good for	Orders, payments, stock	Product pages, logs, settings

Store each fact only once