Пошук уроків, статей та іншого контенту
Навчіться перетворювати вимоги продукту на сутності, атрибути, зв’язки та структуру реляційної схеми.
Схема бази даних описує:
які дані зберігаються;
у яких таблицях вони розміщені;
які поля має кожна таблиця;
як таблиці пов’язані між собою;
які правила захищають дані від помилок.
У реляційній базі даних, зокрема PostgreSQL, основними елементами схеми є:
сутності — об’єкти предметної області;
атрибути — властивості цих об’єктів;
зв’язки — відношення між сутностями;
обмеження — правила для допустимих даних.
Наприклад, у застосунку для керування завданнями сутностями можуть бути користувачі, проєкти, завдання та мітки.
Починати проєктування варто не з SQL, а з вимог продукту.
Уявімо такі вимоги:
Користувач може створити проєкт.
У проєкті може бути кілька учасників.
У проєкті зберігаються завдання.
Завдання можуть призначатися користувачам.
Завдання можуть мати мітки.
Одна мітка може використовуватися в багатьох завданнях.
Зазвичай іменники у вимогах підказують сутності:
користувач;
проєкт;
учасник проєкту;
завдання;
мітка.
Деякі сутності є очевидними об’єктами, а деякі — таблицями для зв’язків. Наприклад, «участь користувача в проєкті» не є окремим об’єктом продукту, але для зберігання зв’язку потрібна таблиця project_members.
Для кожної сутності потрібно визначити її атрибути.
Наприклад, користувач може мати:
ідентифікатор;
ім’я;
електронну адресу;
дату створення.
Проєкт може мати:
ідентифікатор;
назву;
опис;
ідентифікатор власника;
дату створення.
Завдання може мати:
ідентифікатор;
ідентифікатор проєкту;
заголовок;
опис;
статус;
ідентифікатор виконавця;
дату створення.
Для кожного атрибута варто відповісти на такі запитання:
Який тип даних йому потрібен?
Чи може значення бути відсутнім?
Чи має значення бути унікальним?
Чи потрібне значення за замовчуванням?
Чи є допустимий діапазон або набір значень?
Поширені типи PostgreSQL:
integer або bigint — цілі числа;
text — текст довільної довжини;
boolean — true або false;
date — календарна дата;
timestamp — дата й час без часового поясу;
timestamptz — дата й час із урахуванням часового поясу.
Для ідентифікаторів у нових таблицях зручно використовувати:
id bigint GENERATED ALWAYS AS IDENTITYPostgreSQL автоматично генеруватиме послідовні значення для такого поля.
Якщо поле має бути обов’язковим, додають NOT NULL.
Наприклад, проєкт не може існувати без назви:
name text NOT NULLЯкщо завдання може бути створене без призначеного виконавця, поле assignee_id має дозволяти NULL. Відсутність значення в цьому випадку означає, що виконавця ще не призначено.
Первинний ключ однозначно ідентифікує рядок у таблиці.
Зазвичай кожна основна таблиця має поле id:
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEYПервинний ключ:
має бути унікальним;
не може бути NULL;
використовується іншими таблицями для посилання на цей рядок.
Не варто використовувати ім’я або електронну адресу як єдиний первинний ключ. Такі значення можуть змінюватися, а ідентифікатор зазвичай залишається стабільним.
Зовнішній ключ створює зв’язок між таблицями.
Наприклад, кожне завдання належить одному проєкту:
project_id bigint NOT NULL REFERENCES projects(id)Це означає, що значення tasks.project_id має посилатися на наявний рядок у projects.
Зовнішній ключ захищає від ситуації, коли завдання посилається на проєкт, якого не існує.
Зв’язок «один до одного» означає, що одному рядку першої таблиці відповідає не більше одного рядка другої.
Наприклад, користувач і його налаштування можуть бути пов’язані таким чином:
users 1 ─── 1 user_settingsУ таблиці user_settings зовнішній ключ на користувача додатково має бути унікальним.
Зв’язок «один до багатьох» означає, що один рядок першої таблиці може бути пов’язаний із багатьма рядками другої.
Один проєкт може мати багато завдань:
projects 1 ─── N tasksУ такому випадку зовнішній ключ розміщується в таблиці, яка містить багато рядків:
tasks.project_id REFERENCES projects(id)Зв’язок «багато до багатьох» означає, що:
один користувач може бути в багатьох проєктах;
один проєкт може мати багатьох користувачів.
Такий зв’язок не зберігають безпосередньо в одній із основних таблиць. Для нього створюють проміжну таблицю:
users 1 ─── N project_members N ─── 1 projectsТаблиця project_members міститиме два зовнішні ключі:
user_id;
project_id.
Їхня комбінація повинна бути унікальною, щоб один користувач не був доданий до одного проєкту двічі.
Так само завдання і мітки мають зв’язок «багато до багатьох», тому для них потрібна таблиця task_tags.
Для застосунку керування завданнями можна визначити такі таблиці:
app_users — користувачі;
projects — проєкти;
project_members — учасники проєктів;
tasks — завдання;
tags — мітки;
task_tags — зв’язки між завданнями та мітками.
Логічні зв’язки:
користувач може створити багато проєктів;
проєкт має одного власника;
користувач може бути учасником багатьох проєктів;
проєкт може мати багато учасників;
проєкт має багато завдань;
завдання може мати одного виконавця або не мати його;
завдання може мати багато міток;
мітка може бути додана до багатьох завдань.
Нижче наведено повний приклад створення схеми. Його можна виконати в базі даних PostgreSQL.
-- Видаляємо таблиці в порядку, що враховує зовнішні ключі.
DROP TABLE IF EXISTS task_tags;
DROP TABLE IF EXISTS tasks;
DROP TABLE IF EXISTS tags;
DROP TABLE IF EXISTS project_members;
DROP TABLE IF EXISTS projects;
DROP TABLE IF EXISTS app_users;
CREATE TABLE app_users (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL CHECK (length(trim(name)) > 0),
email text NOT NULL UNIQUE,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE projects (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
owner_id bigint NOT NULL REFERENCES app_users(id),
name text NOT NULL CHECK (length(trim(name)) > 0),
description text,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE project_members (
project_id bigint NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
user_id bigint NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
joined_at timestamptz NOT NULL DEFAULT now(),
-- Один користувач може бути учасником проєкту лише один раз.
PRIMARY KEY (project_id, user_id)
);
CREATE TABLE tasks (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
project_id bigint NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
assignee_id bigint REFERENCES app_users(id) ON DELETE SET NULL,
title text NOT NULL CHECK (length(trim(title)) > 0),
description text,
status text NOT NULL DEFAULT 'todo'
CHECK (status IN ('todo', 'in_progress', 'done')),
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE tags (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL UNIQUE CHECK (length(trim(name)) > 0)
);
CREATE TABLE task_tags (
task_id bigint NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
tag_id bigint NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
-- Одна мітка не може бути додана до одного завдання двічі.
PRIMARY KEY (task_id, tag_id)
);У цій схемі:
PRIMARY KEY ідентифікує кожен рядок;
FOREIGN KEY перевіряє зв’язки між таблицями;
NOT NULL забороняє пропущені обов’язкові значення;
UNIQUE не дозволяє дублювати електронні адреси та назви міток;
CHECK обмежує допустимі значення;
DEFAULT автоматично встановлює значення за замовчуванням.
Наприклад, поле status може містити лише:
todo;
in_progress;
done.
Спроба вставити інший статус завершиться помилкою.
Для зовнішніх ключів можна визначити, що робити зі зв’язаними рядками.
ON DELETE CASCADEproject_id bigint REFERENCES projects(id) ON DELETE CASCADEЯкщо видалити проєкт, PostgreSQL автоматично видалить його завдання. Це доречно для даних, які не мають сенсу без батьківського об’єкта.
У прикладі каскадне видалення використовується для:
учасників проєкту;
завдань проєкту;
міток завдання.
ON DELETE SET NULLassignee_id bigint REFERENCES app_users(id) ON DELETE SET NULLЯкщо видалити користувача, завдання не видаляється. У нього просто очищується поле виконавця.
Це підходить для необов’язкового зв’язку, де основний об’єкт має залишитися.
Якщо не вказати ON DELETE, PostgreSQL зазвичай не дозволить видалити рядок, на який посилаються інші таблиці. Така поведінка захищає дані від випадкового порушення зв’язків.
Вибір поведінки залежить від вимог продукту. Не слід додавати CASCADE автоматично: видалення одного рядка може спричинити видалення великої кількості пов’язаних даних.
Нормалізація допомагає уникати дублювання та суперечливих даних.
Наприклад, невдалий варіант таблиці завдань може виглядати так:
tasks
- id
- title
- tag_namesУ tag_names можуть зберігатися значення на кшталт:
"backend, urgent, bug"Це незручно, тому що:
окремі мітки складно шукати;
назви можуть дублюватися;
складно гарантувати однаковий формат;
неможливо коректно використати зовнішній ключ на кожну мітку.
Кращий варіант — окремі таблиці tags і task_tags.
Так само не потрібно зберігати дані користувача в кожному завданні:
tasks
- id
- title
- assignee_name
- assignee_emailЯкщо користувач змінить електронну адресу, доведеться оновлювати багато рядків. Натомість у tasks зберігається лише assignee_id, а дані користувача залишаються в app_users.
Після створення таблиць можна додати кілька рядків і перевірити зв’язки.
-- Створюємо користувачів.
INSERT INTO app_users (name, email)
VALUES
('Олена', 'olena@example.com'),
('Андрій', 'andrii@example.com');
-- Створюємо проєкт від імені Олени.
INSERT INTO projects (owner_id, name, description)
SELECT id, 'Мобільний застосунок', 'Планування першої версії застосунку'
FROM app_users
WHERE email = 'olena@example.com';
-- Додаємо Андрія до проєкту.
INSERT INTO project_members (project_id, user_id)
SELECT
p.id,
u.id
FROM projects AS p
JOIN app_users AS u ON u.email = 'andrii@example.com'
WHERE p.name = 'Мобільний застосунок';
-- Створюємо завдання та призначаємо його Андрію.
INSERT INTO tasks (project_id, assignee_id, title, status)
SELECT
p.id,
u.id,
'Створити екран входу',
'in_progress'
FROM projects AS p
JOIN app_users AS u ON u.email = 'andrii@example.com'
WHERE p.name = 'Мобільний застосунок';
-- Створюємо мітку.
INSERT INTO tags (name)
VALUES ('frontend');
-- Пов’язуємо мітку із завданням.
INSERT INTO task_tags (task_id, tag_id)
SELECT
t.id,
tag.id
FROM tasks AS t
JOIN tags AS tag ON tag.name = 'frontend'
WHERE t.title = 'Створити екран входу';
-- Отримуємо завдання разом із проєктом, виконавцем і міткою.
SELECT
t.title,
t.status,
p.name AS project_name,
u.name AS assignee_name,
tag.name AS tag_name
FROM tasks AS t
JOIN projects AS p ON p.id = t.project_id
LEFT JOIN app_users AS u ON u.id = t.assignee_id
LEFT JOIN task_tags AS tt ON tt.task_id = t.id
LEFT JOIN tags AS tag ON tag.id = tt.tag_id;JOIN використовується для отримання пов’язаних даних із кількох таблиць.
LEFT JOIN у цьому прикладі потрібен для необов’язкових зв’язків. Завдання буде показано навіть тоді, коли для нього ще немає виконавця або мітки.
Під час роботи над новою базою даних корисно діяти послідовно:
Виписати вимоги предметної області.
Знайти основні сутності.
Визначити атрибути кожної сутності.
Додати стабільний первинний ключ до кожної основної таблиці.
Визначити зв’язки між сутностями.
Для зв’язків «багато до багатьох» створити проміжні таблиці.
Вибрати типи даних.
Позначити обов’язкові поля через NOT NULL.
Додати UNIQUE, CHECK і зовнішні ключі.
Визначити поведінку під час видалення.
Перевірити схему реалістичними прикладами даних.
Погано:
member_ids = "4,7,12"Такі дані краще зберігати в окремій таблиці зв’язків.
Без первинного ключа складно однозначно звернутися до конкретного рядка та створити надійні зв’язки з іншими таблицями.
Погано зберігати в tasks ім’я виконавця:
assignee_name = "Андрій"Ім’я може повторюватися або змінюватися. Надійніше зберігати assignee_id.
NULLNULL означає відсутність значення, а не порожній текст чи нуль. Якщо поле за вимогами завжди має бути заповнене, слід додати NOT NULL.
Якщо статус може мати лише кілька значень, це правило потрібно зафіксувати в схемі через CHECK. Перевірка лише в коді застосунку не захищає базу від інших клієнтів або помилок у запитах.
Якщо одна й та сама інформація повторюється в багатьох рядках, зростає ризик, що після оновлення частина рядків залишиться зі старим значенням. Повторювані сутності варто винести в окремі таблиці.
ON DELETE CASCADE може видалити пов’язані дані автоматично. Перед його використанням потрібно переконатися, що таке видалення відповідає правилам продукту.
Проєктування схеми починається з вимог, а не з написання SQL.
Основні кроки:
визначити сутності предметної області;
описати їхні атрибути;
додати первинні ключі;
визначити зв’язки між таблицями;
використовувати зовнішні ключі;
реалізовувати зв’язки «багато до багатьох» через проміжні таблиці;
застосовувати NOT NULL, UNIQUE і CHECK;
продумати поведінку під час видалення;
уникати дублювання та зберігання списків у текстових полях.
Добре спроєктована схема не лише зберігає дані, а й допомагає PostgreSQL підтримувати їхню цілісність.