Практический проект: создаём маленькую базу данных для магазина

Возвращаемся к PostgreSQL.

В предыдущем уроке ты изучил индексы и базовую производительность.

Ты узнал:

• почему запросы могут становиться медленными;
• что такое индексы;
• как EXPLAIN ANALYZE помогает понять запросы;
• почему индексы полезны;
• почему индексы не бесплатные;
• когда индексы могут помочь;
• когда индексы — это просто дорогие украшения.

Очень хорошо.

Теперь пришло время собрать многие части вместе.

Сегодня мы создадим маленький практический проект на PostgreSQL.

Не фейковый пример с одной таблицей.

Не загадочную базу с названиями вроде table1 и data2.

Нет.

Сегодня мы создадим маленькую базу данных для магазина.

Она будет иметь:

customers
categories
products
orders
order_items

Это уже похоже на реальное проектирование базы данных.

Достаточно маленькое, чтобы понять.

Достаточно реальное, чтобы быть полезным.

Как тренировочный меч.

Но для SQL.

Менее опасный.

Обычно.

Что ты изучишь

В этом уроке ты научишься:

• проектировать маленькую реляционную базу данных;
• создавать несколько связанных таблиц;
• использовать первичные ключи;
• использовать внешние ключи;
• добавлять реалистичные данные;
• читать данные с помощью JOIN;
• вычислять суммы заказов;
• считать заказы каждого клиента;
• вычислять общие расходы клиента;
• вычислять продажи по категориям;
• использовать агрегатные функции в практическом проекте;
• создавать полезные индексы;
• думать как проектировщик базы данных.

К концу этого урока у тебя будет рабочий мини-проект базы данных.

Не просто случайные SQL-команды.

А реальная структура.

Маленькая базоданная машина.

С клиентами.

Продуктами.

Заказами.

И достаточным количеством связей, чтобы PostgreSQL почувствовал себя важным.

Идея проекта

Мы создаём маленькую базу данных для магазина.

Магазин продаёт продукты.

Каждый продукт принадлежит к категории.

Клиенты могут создавать заказы.

Каждый заказ может содержать много продуктов.

Это означает, что нам нужны несколько таблиц.

Структура будет такой:

categories
products
customers
orders
order_items

Связи:

categories -> products
customers  -> orders
orders     -> order_items
products   -> order_items

Простыми словами:

Одна категория имеет много продуктов.
Один клиент имеет много заказов.
Один заказ имеет много позиций заказа.
Один продукт может появляться во многих позициях заказов.

Именно здесь реляционные базы данных становятся действительно полезными.

Вместо того чтобы хранить всё в одной огромной хаотичной таблице, мы разделяем данные на чистые таблицы.

Потому что одна огромная таблица кажется простой в начале.

А потом становится кошмаром.

Как положить инструменты, носки, документы, пароли и макароны в один ящик.

Возможно.

Но не мудро.

Подготовь базу данных

Открой PostgreSQL:

sudo -iu postgres psql

Подключись к своей базе данных:

\c learning_postgresql

Если базы ещё нет, создай её:

CREATE DATABASE learning_postgresql;

Потом подключись:

\c learning_postgresql

Теперь удали старые таблицы, если они существуют.

Порядок важен, потому что некоторые таблицы зависят от других.

DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS categories;

Мы удаляем order_items первой, потому что она зависит от orders и products.

PostgreSQL защищает связи.

Как строгий библиотекарь.

Но с большим количеством точек с запятой.

Создаём таблицу Categories

Категория группирует продукты.

Примеры:

Electronics
Furniture
Office
Kitchen

Создай таблицу:

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) UNIQUE NOT NULL
);

Важные детали:

• id — это первичный ключ;
• name является обязательным;
• name должно быть уникальным.

Мы не хотим две категории с названием Electronics.

Одной Electronics достаточно.

Даже для PostgreSQL.

Создаём таблицу Products

Каждый продукт принадлежит к одной категории.

Создай таблицу:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
  stock_quantity INTEGER NOT NULL CHECK (stock_quantity >= 0),
  category_id INTEGER NOT NULL REFERENCES categories(id)
);

Эта таблица имеет:

• название продукта;
• цену продукта;
• количество на складе;
• связь с категорией.

Важная связь:

category_id INTEGER NOT NULL REFERENCES categories(id)

Это означает:

Каждый продукт должен принадлежать к существующей категории.

PostgreSQL не позволит продукту ссылаться на категорию, которой не существует.

Хорошо.

Потому что воображаемые категории не очень полезны для отчётов.

Или для психического спокойствия.

Создаём таблицу Customers

Клиенты делают заказы.

Создай таблицу:

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Важные детали:

• email уникальный;
• name обязательное;
• created_at заполняется автоматически.

В реальных приложениях email часто используется для входа или общения.

Поэтому дубликаты email обычно очень плохая идея.

Две Anna могут существовать.

Два одинаковых email — нет.

PostgreSQL согласен.

Создаём таблицу Orders

Заказ принадлежит одному клиенту.

Создай таблицу:

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  status VARCHAR(50) NOT NULL DEFAULT 'new',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Эта таблица хранит сам заказ.

Но обрати внимание.

Здесь нет колонки total.

Почему?

Потому что сумму можно вычислить из order_items.

Во многих реальных системах суммы тоже сохраняют, для производительности и истории.

Но для обучения лучше их вычислять.

Это показывает, как работают данные.

А PostgreSQL любит учить.

Строго.

Создаём таблицу Order Items

Один заказ может содержать много продуктов.

Например:

Заказ 1:
- Laptop x 1
- Mouse x 2

Именно поэтому нам нужна таблица order_items.

Создай таблицу:

CREATE TABLE order_items (
  id SERIAL PRIMARY KEY,
  order_id INTEGER NOT NULL REFERENCES orders(id),
  product_id INTEGER NOT NULL REFERENCES products(id),
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  unit_price NUMERIC(10, 2) NOT NULL CHECK (unit_price >= 0)
);

Важные детали:

• order_id соединяется с orders;
• product_id соединяется с products;
• quantity должно быть больше нуля;
• unit_price хранит цену продукта на момент заказа.

Почему мы храним unit_price здесь?

Потому что цены продуктов могут измениться позже.

Представь, что продукт сегодня стоит 25.

Завтра он стоит 30.

Старые заказы должны помнить старую цену.

Иначе отчёты превратятся в историческое место преступления.

Поэтому order_items.unit_price хранит цену, использованную в конкретном заказе.

Очень практично.

Очень реально.

Добавляем категории

Добавь категории:

INSERT INTO categories (name)
VALUES
  ('Electronics'),
  ('Furniture'),
  ('Office'),
  ('Kitchen');

Проверь их:

SELECT * FROM categories;

Ты должен увидеть четыре категории.

Маленькое начало.

Цивилизация базы данных начинается здесь.

Добавляем продукты

Добавь продукты:

INSERT INTO products (name, price, stock_quantity, category_id)
VALUES
  ('Laptop', 900.00, 5, 1),
  ('Mouse', 25.00, 30, 1),
  ('Keyboard', 70.00, 15, 1),
  ('Desk Chair', 150.00, 8, 2),
  ('Bookshelf', 120.00, 4, 2),
  ('Notebook', 5.00, 100, 3),
  ('Pen', 2.00, 200, 3),
  ('Whiteboard', 80.00, 3, 3),
  ('Coffee Mug', 12.00, 40, 4),
  ('Kitchen Knife', 35.00, 20, 4);

Проверь продукты:

SELECT * FROM products;

Теперь у нас есть продукты, связанные с категориями.

Но мы всё ещё видим category_id.

Люди предпочитают названия.

Поэтому скоро мы используем JOIN.

Потому что загадочные ID — это не интерфейс пользователя.

Это крик о помощи.

Добавляем клиентов

Добавь клиентов:

INSERT INTO customers (email, name)
VALUES
  ('anna@example.com', 'Anna'),
  ('marco@example.com', 'Marco'),
  ('sofia@example.com', 'Sofia'),
  ('luca@example.com', 'Luca');

Проверь клиентов:

SELECT * FROM customers;

Теперь у нас есть люди, которые могут покупать вещи.

Очень опасно.

Для кошельков.

Добавляем заказы

Создай несколько заказов:

INSERT INTO orders (customer_id, status)
VALUES
  (1, 'paid'),
  (1, 'paid'),
  (2, 'paid'),
  (3, 'new'),
  (4, 'cancelled');

Это означает:

Anna имеет два заказа.
Marco имеет один заказ.
Sofia имеет один новый заказ.
Luca имеет один отменённый заказ.

Проверь заказы:

SELECT * FROM orders;

Заказы существуют.

Но они пустые.

Заказ без продуктов — как коробка от пиццы без пиццы.

Технически коробка.

Эмоционально разочарование.

Добавляем позиции заказов

Теперь добавь продукты к заказам:

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES
  (1, 1, 1, 900.00),
  (1, 2, 2, 25.00),
  (1, 3, 1, 70.00),

  (2, 6, 10, 5.00),
  (2, 7, 20, 2.00),

  (3, 4, 1, 150.00),
  (3, 5, 1, 120.00),

  (4, 9, 2, 12.00),
  (4, 10, 1, 35.00),

  (5, 2, 1, 25.00);

Проверь позиции заказов:

SELECT * FROM order_items;

Теперь у нас есть реальная мини-структура магазина.

Клиенты.

Заказы.

Продукты.

Позиции.

Цены.

Количество.

Связи.

Красивая маленькая машина базы данных.

Показываем продукты с названиями категорий

Начнём с простого JOIN.

SELECT
  p.id,
  p.name AS product_name,
  p.price,
  p.stock_quantity,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id
ORDER BY p.id;

Это даёт читаемые данные продуктов.

Вместо:

Laptop | category_id = 1

получаем:

Laptop | Electronics

Именно это нужно реальным приложениям.

Пользователи не должны расшифровывать ID.

Пользователи и так достаточно страдают.

Особенно когда пароль требует один символ, одну цифру, древнюю руну и эмоциональную стабильность.

Показываем заказы с именами клиентов

Теперь покажи заказы с именами клиентов:

SELECT
  o.id AS order_id,
  c.name AS customer_name,
  c.email,
  o.status,
  o.created_at
FROM orders AS o
JOIN customers AS c
ON o.customer_id = c.id
ORDER BY o.id;

Это показывает, кто сделал каждый заказ.

Очень полезно.

Таблица только с customer_id технически правильная.

Но читаемые данные лучше.

Читаемые данные отделяют хорошую admin panel от пещеры с Excel-файлом.

Показываем полные детали заказов

Теперь покажем позиции заказов с информацией о продукте и клиенте.

SELECT
  o.id AS order_id,
  c.name AS customer_name,
  p.name AS product_name,
  oi.quantity,
  oi.unit_price,
  oi.quantity * oi.unit_price AS item_total
FROM order_items AS oi
JOIN orders AS o
ON oi.order_id = o.id
JOIN customers AS c
ON o.customer_id = c.id
JOIN products AS p
ON oi.product_id = p.id
ORDER BY o.id, oi.id;

Это уже серьёзный запрос.

Он соединяет:

order_items
orders
customers
products

Теперь мы видим:

Какой клиент купил какой продукт.
Сколько штук.
По какой цене.
С какой суммой.

Это сила реляционных баз данных.

Маленькие таблицы.

Правильно соединённые.

Полезные ответы.

Без ящика хаоса.

Вычисляем сумму каждого заказа

Теперь вычислим общую стоимость каждого заказа.

SELECT
  o.id AS order_id,
  c.name AS customer_name,
  o.status,
  SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders AS o
JOIN customers AS c
ON o.customer_id = c.id
JOIN order_items AS oi
ON o.id = oi.order_id
GROUP BY o.id, c.name, o.status
ORDER BY o.id;

Здесь мы используем:

• JOIN;
• SUM;
• GROUP BY.

Очень полезная комбинация.

База данных вычисляет суммы из позиций заказов.

Без калькулятора.

Без нервного Excel-файла.

PostgreSQL справляется.

С достоинством.

Вычисляем только оплаченные заказы

Обычно отменённые заказы не должны считаться доходом.

Поэтому можем фильтровать по статусу.

SELECT
  o.id AS order_id,
  c.name AS customer_name,
  SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders AS o
JOIN customers AS c
ON o.customer_id = c.id
JOIN order_items AS oi
ON o.id = oi.order_id
WHERE o.status = 'paid'
GROUP BY o.id, c.name
ORDER BY order_total DESC;

Это показывает только оплаченные заказы.

Очень важно.

Потому что считать отменённые заказы как доход — отличный способ сделать красивые отчёты и ужасные бизнес-решения.

Красивые числа могут врать.

SQL помогает.

Но только если правильно спрашиваешь.

Общий доход

Теперь вычислим общий доход из оплаченных заказов.

SELECT
  SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM orders AS o
JOIN order_items AS oi
ON o.id = oi.order_id
WHERE o.status = 'paid';

Это возвращает одно число.

Очень бизнесовое число.

Такое число люди ставят на dashboard.

А потом серьёзно на него смотрят.

Даже если dashboard имеет только три строки.

Заказы на клиента

Посчитай, сколько заказов имеет каждый клиент.

SELECT
  c.name AS customer_name,
  COUNT(o.id) AS orders_count
FROM customers AS c
LEFT JOIN orders AS o
ON c.id = o.customer_id
GROUP BY c.name
ORDER BY orders_count DESC;

Мы используем LEFT JOIN, потому что можем хотеть показать также клиентов с нулевым количеством заказов.

В наших текущих данных каждый клиент имеет хотя бы один заказ.

Но в реальной жизни многие клиенты регистрируются и ничего не покупают.

Очень нормально.

Очень больно для маркетинга.

Оплаченные заказы на клиента

Теперь посчитай только оплаченные заказы.

SELECT
  c.name AS customer_name,
  COUNT(o.id) AS paid_orders_count
FROM customers AS c
LEFT JOIN orders AS o
ON c.id = o.customer_id
AND o.status = 'paid'
GROUP BY c.name
ORDER BY paid_orders_count DESC;

Важная деталь:

Условие статуса находится внутри ON:

AND o.status = 'paid'

Почему?

Потому что мы всё ещё хотим видеть всех клиентов.

Если поставить это условие в WHERE, клиенты без оплаченных заказов могут исчезнуть.

Это классическая деталь LEFT JOIN.

Маленькая деталь.

Большая разница.

Ловушка базы данных номер 247.

Собери их все.

Общие расходы клиента

Теперь вычислим, сколько каждый клиент потратил на оплаченные заказы.

SELECT
  c.name AS customer_name,
  COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS total_spent
FROM customers AS c
LEFT JOIN orders AS o
ON c.id = o.customer_id
AND o.status = 'paid'
LEFT JOIN order_items AS oi
ON o.id = oi.order_id
GROUP BY c.name
ORDER BY total_spent DESC;

Здесь мы используем COALESCE.

Почему?

Потому что если клиент не имеет оплаченных заказов, сумма будет NULL.

Мы хотим 0.

Вот это:

COALESCE(SUM(oi.quantity * oi.unit_price), 0)

означает:

Если сумма существует, покажи её.
Если сумма NULL, покажи 0.

Очень практично.

Очень дружелюбно к dashboard.

Очень “не пугай бухгалтера”.

Продажи по категориям

Теперь вычислим продажи по категориям.

SELECT
  cat.name AS category_name,
  SUM(oi.quantity * oi.unit_price) AS total_sales
FROM order_items AS oi
JOIN products AS p
ON oi.product_id = p.id
JOIN categories AS cat
ON p.category_id = cat.id
JOIN orders AS o
ON oi.order_id = o.id
WHERE o.status = 'paid'
GROUP BY cat.name
ORDER BY total_sales DESC;

Это отвечает на вопрос:

Какие категории приносят больше всего дохода?

Очень полезно для бизнеса.

Возможно, победит Electronics.

Возможно, Office.

Возможно, Kitchen удивит всех чашками.

Никогда не недооценивай чашки.

Количество проданных продуктов

Теперь посчитаем, сколько единиц каждого продукта было продано.

SELECT
  p.name AS product_name,
  SUM(oi.quantity) AS units_sold
FROM products AS p
JOIN order_items AS oi
ON p.id = oi.product_id
JOIN orders AS o
ON oi.order_id = o.id
WHERE o.status = 'paid'
GROUP BY p.name
ORDER BY units_sold DESC;

Это полезно для планирования склада.

Если ручки хорошо продаются, закажи больше ручек.

Если книжные полки не продаются, возможно, перестань строить деревянную империю.

Данные помогают принимать решения.

Иногда жёстко.

Продукты, которые никогда не продавались

Теперь найдём продукты, которые никогда не продавались в оплаченных заказах.

SELECT
  p.name AS product_name
FROM products AS p
LEFT JOIN order_items AS oi
ON p.id = oi.product_id
LEFT JOIN orders AS o
ON oi.order_id = o.id
AND o.status = 'paid'
GROUP BY p.id, p.name
HAVING COUNT(o.id) = 0
ORDER BY p.name;

Этот запрос немного сложнее.

Он использует:

• LEFT JOIN;
• GROUP BY;
• HAVING;
• COUNT.

Идея такая:

Покажи продукты, которые имеют ноль оплаченных заказов.

Очень полезно.

Очень бизнесово.

Очень “почему мы купили 500 штук этого?”.

Продукты с низким запасом

Покажи продукты с низким запасом на складе.

SELECT
  name AS product_name,
  stock_quantity
FROM products
WHERE stock_quantity < 10
ORDER BY stock_quantity ASC;

Это простой запрос.

Но очень практичный.

В реальной системе он мог бы питать уведомление:

Низкий запас. Закажи больше.

Базы данных нужны не только для хранения данных.

Они могут помогать замечать проблемы.

Как тихий помощник.

Который говорит только SQL.

Создаём полезные индексы

Теперь добавим индексы, которые имеют смысл в этом проекте.

Первичные ключи уже имеют индексы автоматически.

Но внешние ключи — хорошие кандидаты на индексы.

Создай индексы:

CREATE INDEX idx_products_category_id
ON products(category_id);

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

CREATE INDEX idx_order_items_order_id
ON order_items(order_id);

CREATE INDEX idx_order_items_product_id
ON order_items(product_id);

Эти индексы могут помогать с JOIN.

Теперь создай индексы для частой фильтрации:

CREATE INDEX idx_orders_status
ON orders(status);

CREATE INDEX idx_orders_created_at
ON orders(created_at);

Они могут помогать запросам вроде:

WHERE status = 'paid'

и:

ORDER BY created_at DESC

Не создавай индексы вслепую.

Здесь они имеют смысл, потому что эти колонки, скорее всего, будут появляться в JOIN, фильтрах или сортировке.

Индексы должны следовать реальным запросам.

Не вайбу.

PostgreSQL не оптимизирует вайб.

Мы уже это обсуждали.

Всё ещё трагично.

Проверяем запрос через EXPLAIN ANALYZE

Проанализируем один запрос.

EXPLAIN ANALYZE
SELECT
  o.id AS order_id,
  c.name AS customer_name,
  SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders AS o
JOIN customers AS c
ON o.customer_id = c.id
JOIN order_items AS oi
ON o.id = oi.order_id
WHERE o.status = 'paid'
GROUP BY o.id, c.name
ORDER BY order_total DESC;

Ищи слова вроде:

Seq Scan
Index Scan
Hash Join
Nested Loop
Planning Time
Execution Time

Не паникуй, если вывод выглядит сложным.

Цель не в том, чтобы понять каждую деталь прямо сейчас.

Цель — начать читать план.

PostgreSQL показывает, как он работает.

Как механик, который открывает двигатель.

Сначала страшно.

Потом постепенно начинает иметь смысл.

Потом страшным становишься ты.

В хорошем смысле.

Обновление склада после заказа

В реальных магазинах, когда заказ оплачен, запас на складе должен уменьшаться.

Например, если заказ 1 продал:

Laptop x 1
Mouse x 2
Keyboard x 1

Можем обновить запас вручную:

UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE id = 1;

UPDATE products
SET stock_quantity = stock_quantity - 2
WHERE id = 2;

UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE id = 3;

Потом проверь:

SELECT
  name,
  stock_quantity
FROM products
WHERE id IN (1, 2, 3);

Это работает.

Но ручные обновления опасны.

В реальных приложениях это нужно делать осторожно.

Обычно внутри транзакции.

О безопасных изменениях в базе данных мы поговорим позже.

Потому что базы данных мощные.

А мощность плюс невнимательность равняется интересным катастрофам.

Лучший запрос для обновления склада

Можно обновить запас на основе позиций заказа.

Для заказа 1:

UPDATE products AS p
SET stock_quantity = p.stock_quantity - oi.quantity
FROM order_items AS oi
WHERE p.id = oi.product_id
AND oi.order_id = 1;

Этот запрос обновляет все продукты из заказа 1 за один раз.

Очень полезно.

Очень мощно.

Также опасно, если использовать неправильный order_id.

PostgreSQL послушается.

Он не твоя мама.

Он не спросит:

Ты уверен, дорогой?

Поэтому всегда проверяй условия.

Находим детали заказов одного клиента

Покажи все продукты из оплаченных заказов Anna:

SELECT
  c.name AS customer_name,
  o.id AS order_id,
  p.name AS product_name,
  oi.quantity,
  oi.unit_price,
  oi.quantity * oi.unit_price AS item_total
FROM customers AS c
JOIN orders AS o
ON c.id = o.customer_id
JOIN order_items AS oi
ON o.id = oi.order_id
JOIN products AS p
ON oi.product_id = p.id
WHERE c.email = 'anna@example.com'
AND o.status = 'paid'
ORDER BY o.id;

Такой запрос можно использовать для:

Истории заказов клиента.
Счетов.
Админ-панелей.
Поддержки клиентов.

Очень реально.

Очень полезно.

Очень “скажи мне, что этот клиент купил”.

Поиск продуктов по категории и цене

Покажи продукты из категории Electronics дешевле 100:

SELECT
  p.name AS product_name,
  p.price,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id
WHERE c.name = 'Electronics'
AND p.price < 100
ORDER BY p.price ASC;

Этот запрос мог бы питать фильтр в онлайн-магазине.

Категория.

Цена.

Сортировка.

Очень нормально.

Очень распространено.

Очень хорошее место, чтобы подумать об индексах, если таблица вырастет.

Добавляем индекс на несколько колонок

Если ты часто фильтруешь продукты по категории и сортируешь по цене, этот индекс может помочь:

CREATE INDEX idx_products_category_price
ON products(category_id, price);

Этот индекс полезен для запросов типа:

WHERE category_id = 1
ORDER BY price ASC

Порядок колонок имеет значение.

Индекс начинается с category_id.

Потом идёт price.

Это не случайно.

Это основано на том, как работает запрос.

Индексы — это не наклейки.

Это стратегия.

Маленькая стратегия базы данных.

Как шахматы, но с большим количеством счетов.

Типичные ошибки в маленьких проектах баз данных

Хранить всё в одной таблице

Плохая идея:

customer_name
customer_email
product_name
category_name
order_date
quantity
price

Всё в одной таблице.

Выглядит просто.

В начале.

Потом меняется email клиента.

Потом меняется название категории.

Потом меняется цена продукта.

Потом отчёты становятся странными.

Потом кто-то открывает Excel.

Потом общество рушится.

Используй связанные таблицы.

Именно для этого существуют реляционные базы данных.

Забывать внешние ключи

Без внешних ключей данные могут стать несогласованными.

Например:

Позиция заказа ссылается на продукт 999.
Но продукта 999 не существует.

Плохо.

Внешние ключи защищают связи.

PostgreSQL может охранять дверь.

Позволь ему.

Забывать исторические цены

Если позиции заказа только ссылаются на продукты и не хранят unit_price, старые заказы могут измениться, когда изменится цена продукта.

Обычно это плохо.

Старый заказ должен помнить цену на момент покупки.

История имеет значение.

Особенно когда речь идёт о деньгах.

Считать отменённые заказы как доход

Это классическая ошибка в отчётах.

Всегда думай о статусе.

WHERE o.status = 'paid'

Без этого отчёт дохода может включать отменённые или неоплаченные заказы.

График выглядит лучше.

Реальность — нет.

Реальность обычно побеждает.

Использовать INNER JOIN, когда нужен LEFT JOIN

Если хочешь показать всех клиентов, даже тех, кто не имеет заказов, используй LEFT JOIN.

Если используешь INNER JOIN, клиенты без заказов исчезнут.

Иногда это правильно.

Иногда это тихо ломает отчёт.

SQL не кричит.

Он возвращает именно то, что ты попросил.

Даже если попросил плохо.

Очень честно.

Очень опасно.

Практика

Покажи все продукты с категориями:

SELECT
  p.name AS product_name,
  p.price,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id
ORDER BY c.name, p.name;

Покажи все заказы с клиентами:

SELECT
  o.id AS order_id,
  c.name AS customer_name,
  o.status,
  o.created_at
FROM orders AS o
JOIN customers AS c
ON o.customer_id = c.id
ORDER BY o.id;

Вычисли суммы для каждого заказа:

SELECT
  o.id AS order_id,
  c.name AS customer_name,
  SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders AS o
JOIN customers AS c
ON o.customer_id = c.id
JOIN order_items AS oi
ON o.id = oi.order_id
GROUP BY o.id, c.name
ORDER BY order_total DESC;

Вычисли общий оплаченный доход:

SELECT
  SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM orders AS o
JOIN order_items AS oi
ON o.id = oi.order_id
WHERE o.status = 'paid';

Покажи общие расходы клиентов:

SELECT
  c.name AS customer_name,
  COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS total_spent
FROM customers AS c
LEFT JOIN orders AS o
ON c.id = o.customer_id
AND o.status = 'paid'
LEFT JOIN order_items AS oi
ON o.id = oi.order_id
GROUP BY c.name
ORDER BY total_spent DESC;

Покажи продажи по категориям:

SELECT
  cat.name AS category_name,
  SUM(oi.quantity * oi.unit_price) AS total_sales
FROM order_items AS oi
JOIN products AS p
ON oi.product_id = p.id
JOIN categories AS cat
ON p.category_id = cat.id
JOIN orders AS o
ON oi.order_id = o.id
WHERE o.status = 'paid'
GROUP BY cat.name
ORDER BY total_sales DESC;

Покажи продукты с низким запасом:

SELECT
  name,
  stock_quantity
FROM products
WHERE stock_quantity < 10
ORDER BY stock_quantity ASC;

Запускай эти запросы.

Меняй данные.

Добавляй клиентов.

Добавляй заказы.

Добавляй позиции заказов.

Сломай что-нибудь.

Исправь.

Это и есть обучение.

Очень PostgreSQL.

Очень практично.

Мини-задание

Расширь базу данных магазина.

Добавь новую таблицу suppliers.

Правила:

• один поставщик может поставлять много продуктов;
• каждый продукт может иметь одного поставщика;
• название поставщика обязательно;
• email поставщика должен быть уникальным.

Создай таблицу:

CREATE TABLE suppliers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL
);

Добавь поставщиков:

INSERT INTO suppliers (name, email)
VALUES
  ('Tech Supplier', 'tech@example.com'),
  ('Office Supplier', 'office@example.com'),
  ('Home Supplier', 'home@example.com');

Добавь колонку supplier_id к продуктам:

ALTER TABLE products
ADD COLUMN supplier_id INTEGER REFERENCES suppliers(id);

Обнови продукты:

UPDATE products
SET supplier_id = 1
WHERE category_id = 1;

UPDATE products
SET supplier_id = 2
WHERE category_id = 3;

UPDATE products
SET supplier_id = 3
WHERE category_id IN (2, 4);

Теперь напиши запросы, которые:

• покажут продукты с названием категории и названием поставщика;
• посчитают продукты для каждого поставщика;
• найдут поставщиков, у которых есть продукты с низким запасом;
• вычислят продажи по поставщикам;
• покажут поставщиков, чьи продукты принесли больше 500 в оплаченных продажах.

Подсказка для продаж по поставщикам:

SELECT
  s.name AS supplier_name,
  SUM(oi.quantity * oi.unit_price) AS total_sales
FROM suppliers AS s
JOIN products AS p
ON s.id = p.supplier_id
JOIN order_items AS oi
ON p.id = oi.product_id
JOIN orders AS o
ON oi.order_id = o.id
WHERE o.status = 'paid'
GROUP BY s.name
ORDER BY total_sales DESC;

Это задание добавляет ещё один реалистичный уровень.

Поставщики.

Продукты.

Продажи.

Отчёты.

База данных растёт.

Мозг немного страдает.

Это нормально.

Рост имеет свою цену.

JOIN тоже.

Итог

Сегодня ты создал практический проект на PostgreSQL.

Ты научился:

• проектировать маленькую базу данных для магазина;
• создавать связанные таблицы;
• использовать первичные ключи;
• использовать внешние ключи;
• разделять данные на чистые структуры;
• хранить исторические цены в order_items;
• добавлять практические примеры данных;
• читать связанные данные через JOIN;
• вычислять суммы заказов;
• вычислять доход;
• считать заказы на клиента;
• вычислять расходы клиентов;
• вычислять продажи по категориям;
• находить продукты с низким запасом;
• создавать полезные индексы;
• думать о реальных проблемах отчётности.

Это большой шаг.

PostgreSQL теперь больше не просто теория.

Ты использовал его как настоящий инструмент.

База данных — это не только таблицы.

Это структура.

Связи.

Правила.

Вопросы.

Ответы.

И иногда странные ошибки, которые заставляют тебя переосмысливать жизненные решения.

Но это часть пути.

Следующий урок

В следующем уроке мы посмотрим, как PostgreSQL работает с приложениями.

Поговорим о том, как backend-приложения подключаются к PostgreSQL.

Рассмотрим connection string.

Переменные окружения.

Базовые идеи безопасности.

И почему записывать пароли от базы данных прямо в коде — это ужасная идея.

Очень ужасная.

Такая идея, от которой люди из безопасности просыпаются ночью.