Как читать схему shop: таблицы и связи

База — это набор связанных таблиц

Учебная база shop — данные небольшого интернет-магазина: каталог, покупатели, заказы, оплаты, отзывы и сотрудники. Каждая сущность живёт в своей таблице, а таблицы связаны друг с другом через номера — id.

Почти у каждой таблицы есть колонка id — первичный ключ: уникальный номер строки, как номер паспорта. Двух товаров с одинаковым id быть не может. Колонка вида customer_id или order_id — внешний ключ: ссылка на id в другой таблице. В заказе не повторяют имя и город покупателя, а пишут только его номер: orders.customer_id = 7 значит «заказ сделал покупатель с customers.id = 7». Имя, если оно поменяется, придётся исправить в одном месте, а не во всех заказах.

Восемь таблиц

• categories — категории товаров. Колонки: id, name, parent_id — ссылка на родительскую категорию; у корневых категорий она пустая (NULL).
• products — товары. Колонки: id, name, category_id, price (цена, numeric(10,2) — два знака после запятой), stock (остаток на складе), created_at (дата добавления в каталог, date), is_active (продаётся ли сейчас, boolean).
• customers — покупатели. Колонки: id, first_name, last_name, email (уникален), city, birth_date (может быть NULL), registered_at (момент регистрации, timestamp).
• orders — заказы. Колонки: id, customer_id, status (одно из new, paid, shipped, delivered, cancelled), created_at (момент создания, timestamp), shipped_at (момент отгрузки, NULL, пока заказ не отгружен).
• order_items — позиции заказов: какой товар и сколько штук в каком заказе. Колонки: order_id, product_id, quantity, unit_price (цена за штуку на момент заказа). Своего id здесь нет: строку однозначно определяет пара order_id + product_id.
• payments — платежи по заказам. Колонки: id, order_id, amount (сумма), method (card, cash или sbp), paid_at (момент оплаты).
• reviews — отзывы на товары. Колонки: id, product_id, customer_id, rating (оценка от 1 до 5), text (может быть NULL — оценка без текста), created_at.
• employees — сотрудники магазина. Колонки: id, name, manager_id (руководитель), department, salary, hired_at (дата найма).

Все таблицы лежат в схеме shop, но писать shop.products не нужно — хватит products. Список колонок с типами есть и в панели «Таблицы» в тренажёре: нажми на имя таблицы, а подсказка с описанием колонки появится при наведении.

Чтобы быстро посмотреть, что лежит в таблице, хватит SELECT * с LIMIT:

SELECT * FROM categories ORDER BY id LIMIT 5

 id |    name     | parent_id
----+-------------+-----------
  1 | Электроника |
  2 | Книги       |
  3 | Дом и сад   |
  4 | Смартфоны   |         1
  5 | Ноутбуки    |         1
(5 rows)

У «Электроники», «Книг» и «Дома и сада» parent_id пустой — это корневые категории. «Смартфоны» и «Ноутбуки» ссылаются на категорию 1, то есть лежат внутри «Электроники».

А так — узнать, сколько строк в каждой таблице:

SELECT 'categories' AS table_name, COUNT(*) AS row_count FROM categoriesUNION ALL SELECT 'products', COUNT(*) FROM productsUNION ALL SELECT 'customers', COUNT(*) FROM customersUNION ALL SELECT 'orders', COUNT(*) FROM ordersUNION ALL SELECT 'order_items', COUNT(*) FROM order_itemsUNION ALL SELECT 'payments', COUNT(*) FROM paymentsUNION ALL SELECT 'reviews', COUNT(*) FROM reviewsUNION ALL SELECT 'employees', COUNT(*) FROM employees

 table_name  | row_count
-------------+-----------
 categories  |        10
 products    |        31
 customers   |        50
 orders      |       200
 order_items |       509
 payments    |       177
 reviews     |       120
 employees   |        15
(8 rows)

COUNT(*) считает строки, UNION ALL склеивает результаты восьми запросов в одну таблицу; порядок строк здесь совпадает с порядком частей только потому, что PostgreSQL выполняет их по очереди, — надёжно задаёт порядок только ORDER BY. Подробнее — карточки count и union; здесь важен масштаб: база маленькая, любую таблицу можно просмотреть целиком.

Связи между таблицами

Стрелка читается «ссылается на»:

• products.category_id → categories.id — товар лежит в категории;
• categories.parent_id → categories.id — категория вложена в другую категорию той же таблицы;
• orders.customer_id → customers.id — заказ сделал покупатель;
• order_items.order_id → orders.id — позиция относится к заказу;
• order_items.product_id → products.id — в позиции указан товар;
• payments.order_id → orders.id — платёж по заказу;
• reviews.product_id → products.id и reviews.customer_id → customers.id — отзыв о товаре, оставленный покупателем;
• employees.manager_id → employees.id — руководитель сотрудника — тоже сотрудник; у директора manager_id пустой.

Связь «один ко многим» читается так: у одного покупателя может быть много заказов, но у каждого заказа ровно один покупатель. Поэтому ссылка customer_id хранится в orders, а не наоборот. order_items — связующая таблица между orders и products: в одном заказе бывает много товаров, а один товар встречается во многих заказах.

Чтобы увидеть вместо номера название, таблицы соединяют по связи — через JOIN:

SELECT p.id, p.name, p.category_id, c.name AS categoryFROM products AS pJOIN categories AS c ON c.id = p.category_idORDER BY p.idLIMIT 5

 id |        name         | category_id | category
----+---------------------+-------------+-----------
  1 | Смартфон Nova X     |           4 | Смартфоны
  2 | Смартфон Nova Lite  |          10 | Бюджетные
  3 | Смартфон Pixel Pro  |           9 | Флагманы
  4 | Смартфон Galaxy S   |           9 | Флагманы
  5 | Смартфон Redmi Note |          10 | Бюджетные
(5 rows)

ON c.id = p.category_id — это и есть связь из списка выше: к каждому товару подставляется строка категории с тем же номером. p и c — короткие имена таблиц, чтобы не писать products.name и categories.name целиком.

По той же схеме разворачивают заказ: order_items ссылается на товары, и названия берутся из products.

SELECT oi.order_id, oi.product_id, p.name, oi.quantity, oi.unit_priceFROM order_items AS oiJOIN products AS p ON p.id = oi.product_idWHERE oi.order_id = 7ORDER BY oi.product_id

 order_id | product_id |        name        | quantity | unit_price
----------+------------+--------------------+----------+------------
        7 |         10 | Наушники Beat Mini |        2 |    3384.91
        7 |         16 | Linux с нуля       |        3 |    1496.23
        7 |         20 | Postman и API      |        3 |    1104.97
(3 rows)

В заказе № 7 три товара. Цена за штуку берётся из order_items.unit_price, а не из products.price: цена в каталоге может измениться, а в заказе остаётся та, по которой купили.

Как читать незнакомую схему

• Начни с таблицы, о которой вопрос, и посмотри на её колонки: id — сама строка, …_id — ссылки на другие таблицы.
• По имени ссылки найди таблицу: customer_id ведёт в customers, order_id — в orders.
• Прежде чем писать сложный запрос, посмотри несколько строк каждой таблицы через SELECT * … LIMIT 5 — так быстрее понять, что в них лежит и где бывают пустые значения.

Подробнее о конструкциях — в карточках select, where и join.