Natural keys and denormalization

In short: A natural key comes from the data itself, like an email, a surrogate key is a number with no meaning, and denormalization is a copy of data kept on purpose.

Problem

Maria changed her email

If you design the customers yourself, the email looks like a good primary key. It is unique, every customer has one, it is what they log in with. Here is such a design: customers are keyed by email, and orders point to the email.

customers

• PKemailtext
• nametext *
• citytext

orders

• PKidinteger
• FKcustomer_emailtext *→ customers
• ordered_attext *

Maria changes her email:

SQLempty database

UPDATE customers SET email = 'maria.i@example.com'
WHERE email = 'maria.ivanova@example.com';

UPDATE customers SET email = 'maria.i@example.com' WHERE email = 'maria.ivanova@example.com';

▶ RunRestore the queryFresh database

The database refuses the change, because three orders point to the old email. If you change those too, there are still the invoices already sent, the reports and every other system that has remembered the old key.

The idea

Natural and surrogate keys

A key taken from the world (an email, a phone number, a book's number) is a natural key. A number the database assigns itself, with no meaning outside it, is a surrogate key. The id in the shop is one.

A surrogate key never changes, because nothing in the world changes it. So the email changes on one row, and the orders do not notice:

SQLdatabase: shop

UPDATE customers SET email = 'maria.i@example.com' WHERE id = 1;

SELECT c.name, c.email, COUNT(*) AS orders
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.id = 1
GROUP BY c.id, c.name, c.email;

UPDATE customers SET email = 'maria.i@example.com' WHERE id = 1; SELECT c.name, c.email, COUNT(*) AS orders FROM customers c JOIN orders o ON o.customer_id = c.id WHERE c.id = 1 GROUP BY c.id, c.name, c.email;

▶ RunRestore the queryFresh database

The email keeps its rule. It is no longer the key, but it is UNIQUE, so two customers cannot have the same email:

SQLdatabase: shop

UPDATE customers SET email = 'georgi.petrov@example.com' WHERE id = 1;

UPDATE customers SET email = 'georgi.petrov@example.com' WHERE id = 1;

▶ RunRestore the queryFresh database

What about a national ID number (in Bulgaria, the EGN)? No. It is personal data, and the key gets copied into every table that points to the customer. A customer from abroad usually does not have one. And the shop has no need to know it.

More precisely, if you are curious

ON UPDATE CASCADE in REFERENCES makes the database also change the rows that point to the old key. That helps inside the database, but not with the copies of the key outside it: in emails, in logs, in another system.

A natural key fits when the value really never changes and is known from outside. Examples are a currency code (EUR) or a country code (BG).

A UUID is another kind of surrogate key: a 128-bit number written as 550e8400-e29b-41d4-a716-446655440000. It can be made anywhere, without asking the database (in the app, on a phone with no internet), two databases can be merged without clashes, and the address /orders/1043 no longer tells how many orders the shop has. The cost is that it is longer (16 bytes against 8 for a big integer), and random UUIDs land in random places in the index and slow it down. That is why there are also time-ordered UUIDs (version 7). PostgreSQL has a uuid type and a gen_random_uuid() function. SQLite has no separate type and keeps it as text or as a BLOB.

Try it

A new email

Customer 12, Boris, writes that his new email is boris.m@example.com. Fix it.

SQLdatabase: shop

Check▶ Run

The idea

The price in the order row

order_items has unit_price, and products has price. That looks like a repeat that normalisation removes. Raise the price of the coffee:

SQLdatabase: shop

UPDATE products SET price = 15.9 WHERE id = 17;

SELECT oi.order_id, oi.unit_price, p.price AS today
FROM order_items oi
JOIN products p ON p.id = oi.product_id
WHERE oi.product_id = 17;

UPDATE products SET price = 15.9 WHERE id = 17; SELECT oi.order_id, oi.unit_price, p.price AS today FROM order_items oi JOIN products p ON p.id = oi.product_id WHERE oi.product_id = 17;

▶ RunRestore the queryFresh database

The old orders remember 14.90 €, while the product costs 15.90 €. These are not two copies of one fact, but two different facts: “what it costs today” and “what the customer paid”. If the order took its price from products, every price rise would change past bills too.

1. The coffee in products now costs 15.90 €. What should the old order 8 show?

15.90 €, because that is the price nowIt depends on when the report is opened14.90 €, because that is the price it was bought at

Check

The idea

Denormalisation

The order total is different. It really is computed from the items. But the list of orders on the website shows it for every order, and instead of adding up the items every time, you can keep it in orders:

SQLdatabase: shop

ALTER TABLE orders ADD COLUMN total REAL;

UPDATE orders SET total = (
  SELECT SUM(quantity * unit_price) FROM order_items
  WHERE order_items.order_id = orders.id
);

SELECT id, total FROM orders WHERE id >= 58;

ALTER TABLE orders ADD COLUMN total REAL; UPDATE orders SET total = ( SELECT SUM(quantity * unit_price) FROM order_items WHERE order_items.order_id = orders.id ); SELECT id, total FROM orders WHERE id >= 58;

▶ RunRestore the queryFresh database

A deliberate copy of data that could be computed is called denormalisation. You do it when reads are much more frequent than changes and the computation is expensive.

See it

The total that lies

A colleague adds coffee to order 60 but forgets about total:

SQLdatabase: shop

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (60, 17, 1, 14.9);

SELECT o.id, o.total, SUM(oi.quantity * oi.unit_price) AS items
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.id = 60
GROUP BY o.id, o.total;

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (60, 17, 1, 14.9); SELECT o.id, o.total, SUM(oi.quantity * oi.unit_price) AS items FROM orders o JOIN order_items oi ON oi.order_id = o.id WHERE o.id = 60 GROUP BY o.id, o.total;

▶ RunRestore the queryFresh database

The website shows 14 €, while the items add up to 28.90 €. Nothing in the database stopped this. The cost of the copy is that every change to the items must also change the total: in the program, in every place that writes to order_items, or with a trigger (the transactions module).

Exercise

Two tasks

1. Accounting suspects some totals are wrong. Find the orders where total is not equal to the sum of the items. Show the number, the stored total and the real one.

The orders with a wrong total:

SQLdatabase: shop

Check▶ Run

2. Fix the totals of the two orders by computing them again from the items.

Update total for orders 59 and 60:

SQLdatabase: shop

Check▶ Run

Test

Check yourself

1. Which one is a surrogate key?

id INTEGER PRIMARY KEY, which the database assigns itselfThe customer's emailA national ID number

2. The shop uses id as the key. Should the email still be UNIQUE?

Yes, otherwise two customers could have the same emailOnly if it is NOT NULLNo, there is already a key

3. Why does unit_price in order_items not break normalisation?

Because it is a different fact: the price on the day of purchase, not today’sBecause it is a numberIt does break it, but that is allowed

4. A total column in orders keeps the sum of the items. What is the cost?

Every change to the items must also change total, or the sum is wrongOrders become read-onlyNone, it only speeds things up

Check

Start the lesson over

Related lessons

• Builds on: Foreign keys: links between tables · ALTER TABLE: changing a table · Relationship types: 1:1, 1:N, M:N · Normalization
• Continues in: OLTP and OLAP · Document databases · Columnar and time series databases · The bakery, stage 1: the database and the tables
• Tasks on this topic: Designing a database