Проектирование базы данных: от ТЗ до DDL

Этапы проектирования базы данных, выделение сущностей и связей, разрешение связи «многие ко многим», выбор ключей, написание DDL с ограничениями целостности.

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

Этапы проектирования

Выделение сущностей

Практический приём: выпишите существительные из технического задания. Те, у которых есть собственные характеристики и о которых нужно хранить историю, становятся сущностями. Остальные — атрибутами.

Фрагмент ТЗ:
«Учёт заказов в мастерской. Клиент оформляет заказ на ремонт
оборудования. В заказе указывается несколько неисправностей,
для устранения каждой требуются запчасти. Работу выполняет мастер.»

Сущности:  Клиент, Заказ, Оборудование, Неисправность, Запчасть, Мастер
Атрибуты Заказа: номер, дата приёма, дата выдачи, статус, сумма
Связи:
  Клиент  —(1:М)—  Заказ
  Мастер  —(1:М)—  Заказ
  Заказ   —(1:М)—  Позиция работ
  Позиция —(М:М)—  Запчасть   → нужна связующая таблица

Типы связей

Выбор первичного ключа

• Естественный ключ — существующий атрибут: ИНН, серийный номер, артикул. Понятен людям, но может измениться.
• Суррогатный ключ — искусственный автоинкрементный идентификатор. Компактен, неизменен, не зависит от предметной области.
• Составной ключ — из нескольких столбцов, типичен для связующих таблиц.
• Практика: суррогатный ключ для основных таблиц плюс уникальный индекс на естественный ключ. Так вы получаете и стабильность, и контроль дубликатов.

DDL со всеми ограничениями

CREATE TABLE clients (
    id          SERIAL PRIMARY KEY,
    full_name   VARCHAR(150) NOT NULL,
    phone       VARCHAR(20)  NOT NULL UNIQUE,
    email       VARCHAR(100),
    created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_email CHECK (email IS NULL OR email LIKE '%_@_%._%')
);

CREATE TABLE masters (
    id        SERIAL PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    grade     SMALLINT     NOT NULL CHECK (grade BETWEEN 1 AND 6),
    hired_at  DATE         NOT NULL
);

CREATE TABLE orders (
    id           SERIAL PRIMARY KEY,
    client_id    INTEGER     NOT NULL REFERENCES clients(id) ON DELETE RESTRICT,
    master_id    INTEGER     REFERENCES masters(id) ON DELETE SET NULL,
    accepted_at  DATE        NOT NULL DEFAULT CURRENT_DATE,
    finished_at  DATE,
    status       VARCHAR(20) NOT NULL DEFAULT 'new'
                 CHECK (status IN ('new','in_work','done','cancelled')),
    total        NUMERIC(10,2) NOT NULL DEFAULT 0 CHECK (total >= 0),
    CONSTRAINT chk_dates CHECK (finished_at IS NULL OR finished_at >= accepted_at)
);

CREATE TABLE parts (
    id       SERIAL PRIMARY KEY,
    article  VARCHAR(50)  NOT NULL UNIQUE,
    name     VARCHAR(200) NOT NULL,
    price    NUMERIC(10,2) NOT NULL CHECK (price > 0),
    in_stock INTEGER      NOT NULL DEFAULT 0 CHECK (in_stock >= 0)
);

-- Связующая таблица «многие ко многим» со своими атрибутами
CREATE TABLE order_parts (
    order_id   INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    part_id    INTEGER NOT NULL REFERENCES parts(id)  ON DELETE RESTRICT,
    quantity   INTEGER NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(10,2) NOT NULL,   -- цена на момент заказа, не текущая
    PRIMARY KEY (order_id, part_id)
);

CREATE INDEX idx_orders_client ON orders(client_id);
CREATE INDEX idx_orders_status ON orders(status) WHERE status <> 'done';
CREATE INDEX idx_orders_dates  ON orders(accepted_at);

Действия при удалении

Представления и триггеры

-- Представление для отчёта: скрывает сложное соединение
CREATE VIEW v_order_summary AS
SELECT o.id, o.accepted_at, c.full_name AS client,
       m.full_name AS master, o.status,
       COALESCE(SUM(op.quantity * op.unit_price), 0) AS parts_total
FROM orders o
JOIN clients c        ON c.id = o.client_id
LEFT JOIN masters m   ON m.id = o.master_id
LEFT JOIN order_parts op ON op.order_id = o.id
GROUP BY o.id, o.accepted_at, c.full_name, m.full_name, o.status;

-- Триггер: пересчёт суммы заказа при изменении позиций
CREATE OR REPLACE FUNCTION recalc_order_total() RETURNS TRIGGER AS $$
BEGIN
    UPDATE orders SET total = (
        SELECT COALESCE(SUM(quantity * unit_price), 0)
        FROM order_parts WHERE order_id = COALESCE(NEW.order_id, OLD.order_id)
    ) WHERE id = COALESCE(NEW.order_id, OLD.order_id);
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_recalc
AFTER INSERT OR UPDATE OR DELETE ON order_parts
FOR EACH ROW EXECUTE FUNCTION recalc_order_total();

Проверка проекта перед сдачей

1. У каждой таблицы есть первичный ключ.
2. Все связи закрыты внешними ключами с осмысленным ON DELETE.
3. Схема в третьей нормальной форме, а сознательная денормализация обоснована в тексте.
4. Обязательные поля помечены NOT NULL, диапазоны значений закрыты CHECK.
5. Денежные суммы хранятся в NUMERIC, а не в FLOAT — иначе появятся копейки округления.
6. Даты и время — в типах DATE и TIMESTAMP, а не строками.
7. Созданы индексы на внешние ключи и поля частых отборов.
8. Заполнены тестовые данные — хотя бы по десятку строк на таблицу, чтобы запросы что-то возвращали.

Отдельно про NUMERIC: тип FLOAT хранит значения приближённо, и сумма 0,1 + 0,2 в нём не равна 0,3. Для денег это недопустимо, и вопрос о выборе типа для суммы задают на защите почти всегда.