Естествен ключ и денормализация

Накратко: Естественият ключ идва от самите данни, като имейла, изкуственият е номер без значение, а денормализацията е копие на данни, пазено нарочно.

Проблем

Мария смени имейла си

Ако проектираш клиентите сам, имейлът изглежда добър първичен ключ. Уникален е, всеки клиент го има, с него се влиза в сайта. Ето такъв проект: ключът на клиента е имейлът, а поръчките сочат към имейла.

customers

• PKemailtext
• nametext *
• citytext

orders

• PKidinteger
• FKcustomer_emailtext *→ customers
• ordered_attext *

Мария сменя имейла си:

SQLпразна база

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';

▶ ИзпълниВърни заявкатаНова база

Базата не пуска промяната, защото три поръчки сочат към стария имейл. Ако смениш и тях, остават фактурите, които вече са пратени, отчетите и всяка друга система, която е запомнила стария ключ.

Идеята

Естествен и изкуствен ключ

Ключ, взет от света (имейл, телефон, номер на книга), е естествен ключ. Число, което базата дава сама и което няма смисъл извън нея, е изкуствен ключ (на английски surrogate key). Такъв е id в магазина.

Изкуственият ключ не се сменя никога, защото нищо в света не го променя. Затова имейлът се сменя на един ред, а поръчките не забелязват:

SQLбаза: магазин

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;

▶ ИзпълниВърни заявкатаНова база

Имейлът не губи правилото си. Той вече не е ключ, но е UNIQUE, затова двама клиенти не могат да имат един имейл:

SQLбаза: магазин

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

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

▶ ИзпълниВърни заявкатаНова база

А ЕГН? Не. То е лични данни, а ключът се копира във всяка таблица, която сочи към клиента. Клиент от чужбина обикновено няма ЕГН. И магазинът няма нужда да го знае.

По-точно, за любопитните

ON UPDATE CASCADE в REFERENCES кара базата да смени и редовете, които сочат към стария ключ. Това помага вътре в базата, но не и за копията на ключа извън нея: в писма, в логове, в друга система.

Естественият ключ е на място, когато стойността наистина не се мени и се знае отвън. Пример е кодът на валутата (EUR) или на държавата (BG).

UUID е друг вид изкуствен ключ: 128-битово число, записвано като 550e8400-e29b-41d4-a716-446655440000. Прави се навсякъде, без да питаш базата (в приложението, в телефона без интернет), две бази могат да се слеят без сблъсък, а адресът /orders/1043 вече не казва колко поръчки има магазинът. Цената е, че е по-дълъг (16 байта срещу 8 за голямо цяло число), а случайните UUID се пишат на случайни места в индекса и го забавят. Затова има и подредени по време UUID (версия 7). PostgreSQL има тип uuid и функция gen_random_uuid(). SQLite няма отделен тип и го пази като текст или като BLOB.

Пробвай

Нов имейл

Клиент 12, Борис, пише, че новият му имейл е boris.m@example.com. Поправи го.

SQLбаза: магазин

Провери▶ Изпълни

Идеята

Цената в реда на поръчката

В order_items има unit_price, а в products има price. Това изглежда като повторение, което нормализацията маха. Вдигни цената на кафето:

SQLбаза: магазин

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;

▶ ИзпълниВърни заявкатаНова база

Старите поръчки помнят 14,90 €, а продуктът струва 15,90 €. Това не са две копия на един факт, а два различни факта: „колко струва днес“ и „колко е платил клиентът“. Ако поръчката взимаше цената от products, всяко поскъпване щеше да промени и миналите сметки.

1. Цената на кафето в products стана 15,90 €. Какво трябва да покаже старата поръчка 8?

15,90 €, защото това е цената сега14,90 €, защото на тази цена е купеноЗависи от това кога се отваря отчетът

Провери

Идеята

Денормализация

Сумата на поръчката е друго. Тя наистина се смята от редовете. Но списъкът с поръчки в сайта я показва за всяка, и вместо да събираш редовете всеки път, можеш да я пазиш в orders:

SQLбаза: магазин

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;

▶ ИзпълниВърни заявкатаНова база

Съзнателното копие на данни, които могат да се изчислят, се казва денормализация. Прави се, когато четенето е много по-често от промяната и сметката е скъпа.

Виж го

Сумата, която лъже

Колега добавя кафе в поръчка 60, но забравя за total:

SQLбаза: магазин

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;

▶ ИзпълниВърни заявкатаНова база

Сайтът показва 14 €, а редовете дават 28,90 €. Нищо в базата не е спряло това. Цената на копието е, че всяка промяна на редовете трябва да промени и сумата: в програмата, на всяко място, което пише в order_items, или с тригер (модулът за транзакции).

Упражнение

Две задачи

1. Счетоводството подозира, че някои суми не са верни. Намери поръчките, при които total не е равно на сбора на редовете. Покажи номера, записаната сума и истинската.

Поръчките с грешна сума:

SQLбаза: магазин

Провери▶ Изпълни

2. Поправи сумите на двете поръчки, като ги сметнеш наново от редовете.

Обнови total на поръчки 59 и 60:

SQLбаза: магазин

Провери▶ Изпълни

Тест

Провери се

1. Кое е изкуствен (surrogate) ключ?

id INTEGER PRIMARY KEY, което базата дава самаИмейлът на клиентаЕГН

2. Магазинът ползва id като ключ. Трябва ли имейлът все пак да е UNIQUE?

Само ако е NOT NULLНе, вече има ключДа, иначе двама клиенти ще могат да имат един имейл

3. Защо unit_price в order_items не нарушава нормализацията?

Защото е друг факт: цената в деня на покупката, а не днешнатаЗащото е числоЗащото нарушава, но е разрешено

4. Колона total в orders пази сбора на редовете. Каква е цената?

Всяка промяна на редовете трябва да промени и total, иначе сумата лъжеПоръчките стават само за четенеНикаква, само ускорява

Провери

Започни урока отначало

Свързани уроци

• Нужно отпреди: Външни ключове: връзки между таблиците · ALTER TABLE: промяна на таблица · Видове връзки: 1:1, 1:N, M:N · Нормализация
• Продължава в: OLTP и OLAP · Документни бази · Колонни и времеви бази · Пекарната, етап 1: базата и таблиците
• Задачи по темата: Проектиране на база