Итоговый мини-проект: проектирование и оптимизация схемы БД для реального приложения

Введение

В заключительной статье всего 50-статейного SQL-цикла собираем итоговый проект — полное проектирование схемы БД для реалистичного приложения (интернет-магазин), демонстрирующее применение большинства разобранных тем цикла в их естественной, взаимосвязанной комбинации: от нормализации (статья 402) через индексирование (статьи 413-415) до безопасности (статьи 433-435) и практического использования из Qt (статья 437).

Концепция

Архитектура проекта: нормализованная базовая схема (клиенты, товары, заказы, позиции заказа) с правильно спроектированными внешними ключами (статья 397), дополненная осознанной, точечной денормализацией (статьи 403, 420) для конкретных, часто читаемых агрегатов, проиндексированная согласно реальным, ожидаемым паттернам запросов (статьи 413-415), защищённая через роли с принципом наименьших привилегий (статья 434) и параметризованные запросы (статья 433), с системой миграций (статья 428) для управляемого развития схемы во времени.

Пример кода: Итоговый мини-проект

-- Базовая, нормализованная схема (статьи 393-402)
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    registered_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock_quantity INTEGER NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
    attributes JSONB -- для разных категорий товаров с разным набором атрибутов (статья 423)
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    total_amount DECIMAL(12, 2) NOT NULL DEFAULT 0, -- денормализованная сумма (статья 403)
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    price_at_purchase DECIMAL(10, 2) NOT NULL -- "снимок" цены на момент покупки (статья 402)
);

-- Индексы, основанные на ожидаемых паттернах запросов (статьи 413-415)
CREATE INDEX idx_orders_customer ON orders(customer_id);           -- "заказы конкретного клиента"
CREATE INDEX idx_orders_status_created ON orders(status, created_at); -- "необработанные заказы по дате"
CREATE INDEX idx_order_items_order ON order_items(order_id);         -- для JOIN при отображении заказа
CREATE INDEX idx_products_attributes ON products USING GIN (attributes); -- поиск по атрибутам товара

-- Триггер поддержания денормализованной суммы заказа (статьи 403, 410)
CREATE OR REPLACE FUNCTION recalculate_order_total()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE orders SET total_amount = (
        SELECT COALESCE(SUM(quantity * price_at_purchase), 0)
        FROM order_items 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_order_total
AFTER INSERT OR UPDATE OR DELETE ON order_items
FOR EACH ROW EXECUTE FUNCTION recalculate_order_total();

-- Роли с принципом наименьших привилегий (статья 434)
CREATE ROLE shop_app LOGIN PASSWORD 'from_secure_storage';
GRANT SELECT, INSERT, UPDATE ON customers, products, orders, order_items TO shop_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO shop_app; -- для SERIAL-столбцов
-- shop_app НЕ имеет прав DELETE на customers/products — удаление через отдельный, более привилегированный путь

CREATE ROLE shop_analytics LOGIN PASSWORD 'another_secure_password';
GRANT SELECT ON orders, order_items, products TO shop_analytics; -- только чтение для аналитики

// Использование из Qt-приложения — параметризованный запрос, защищённая роль (статьи 433-434, 437)
void createOrder(const QString &customerEmail, const QVector<QPair<int, int>> &itemsProductIdQuantity)
{
    QSqlDatabase db = QSqlDatabase::database("shop_app_connection");
    db.transaction(); // статья 408 — атомарность создания заказа со всеми его позициями

    QSqlQuery customerQuery(db);
    customerQuery.prepare("SELECT id FROM customers WHERE email = :email");
    customerQuery.bindValue(":email", customerEmail);
    customerQuery.exec();
    customerQuery.next();
    int customerId = customerQuery.value(0).toInt();

    QSqlQuery orderQuery(db);
    orderQuery.prepare("INSERT INTO orders (customer_id) VALUES (:customer_id) RETURNING id");
    orderQuery.bindValue(":customer_id", customerId);
    orderQuery.exec();
    orderQuery.next();
    int orderId = orderQuery.value(0).toInt();

    for (const auto &item : itemsProductIdQuantity) {
        QSqlQuery itemQuery(db);
        itemQuery.prepare("INSERT INTO order_items (order_id, product_id, quantity, price_at_purchase) "
                           "SELECT :order_id, :product_id, :quantity, price FROM products WHERE id = :product_id");
        itemQuery.bindValue(":order_id", orderId);
        itemQuery.bindValue(":product_id", item.first);
        itemQuery.bindValue(":quantity", item.second);
        itemQuery.exec();
    }

    db.commit(); // триггер trg_recalc_order_total автоматически пересчитает total_amount
}

Пояснения к коду

Схема объединяет нормализованную структуру (отдельные таблицы для клиентов, товаров, заказов, позиций — статьи 393-402) с осознанной точечной денормализацией именно там, где это оправдано (total_amount в orders для быстрого отображения списка заказов без агрегации при каждом запросе, статья 403; price_at_purchase в order_items как намеренный, неизменяемый исторический снимок цены, статья 402) — обе денормализации сопровождаются явным механизмом поддержания согласованности (триггер для суммы) или явным пониманием, что снимок цены НЕ должен синхронизироваться с последующими изменениями цены товара. Индексы выбраны не абстрактно, а исходя из конкретных, явно названных в комментариях ожидаемых паттернов запросов (статья 414), включая GIN-индекс для JSONB-атрибутов товаров (статья 418, 423) для категорий с переменным набором характеристик. Разделение ролей shop_app/shop_analytics (статья 434) показывает практическое применение принципа наименьших привилегий — основное приложение не имеет прав DELETE на ключевые таблицы (что вынуждает реализовывать логику «удаления» через явное изменение статуса, а не физическое удаление строк, что также полезно для аудита), а аналитическая роль имеет доступ только на чтение, без какой-либо возможности изменения данных.

Подводные камни

• Применение всех техник цикла одновременно и без разбора для каждого нового проекта, без анализа, какие из них реально нужны для конкретного, текущего масштаба и требований этого конкретного проекта — итоговый проект статьи демонстрирует осознанное, целенаправленное применение конкретных техник для конкретных, обоснованных нужд (денормализация именно для часто читаемого агрегата, конкретные индексы под конкретные паттерны запросов), а не механическое, бездумное применение «максимума всех изученных приёмов» без учёта их реальной необходимости в каждом отдельном случае — этот принцип последовательно подчёркивался на протяжении всего цикла и остаётся центральным выводом всего 50-статейного курса.
• Отсутствие плана дальнейшего развития схемы по мере роста проекта — представленная схема разумна для начального, умеренного масштаба, но реальный, растущий проект со временем может столкнуться с потребностью в партиционировании (статья 419) больших таблиц заказов, более продвинутой системе кеширования, или даже шардинге (статья 430) при достижении действительно большого масштаба — эта схема — отправная точка, а не финальное, неизменное решение, и архитектура БД должна планироваться с пониманием возможности будущей эволюции, поддерживаемой системой миграций (статья 428).
• Недостаточное тестирование (статья 439) триггера пересчёта суммы заказа на граничных случаях (заказ без единой позиции, удаление всех позиций существующего заказа, параллельное изменение позиций одного и того же заказа из нескольких одновременных транзакций, статья 409) — денормализованные значения, поддерживаемые через триггеры, требуют особенно тщательного тестирования именно граничных и конкурентных сценариев, поскольку ошибка в логике поддержания согласованности может остаться незамеченной в обычном, «счастливом пути» тестирования, но проявиться неожиданно в реальном, более разнообразном production-использовании.
• Отсутствие явного учёта вопросов резервного копирования (статья 431) и мониторинга производительности (статьи 416-417) с самого начала проектирования, рассматривая их как нечто, что можно «добавить потом» — хотя сама схема БД может быть спроектирована корректно с точки зрения структуры данных, полноценная, надёжная эксплуатация в production требует с самого начала продуманной стратегии резервного копирования и постоянного, не разового мониторинга производительности по мере роста реальной нагрузки, и откладывание этих аспектов «на потом» нередко приводит к болезненным, неприятным открытиям уже после того, как проект достиг production и накопил реальные, ценные данные, которые сложнее защитить и оптимизировать постфактум, чем спланировать заранее.

Углублённый цикл SQL (50 статей, 393-442) полностью завершён — пять блоков провели путь от базовых принципов реляционной модели через продвинутые запросы, производительность, особенности конкретных СУБД, до безопасности и практического применения в реальном Qt-приложении. Общий план статей теперь включает 442 темы, охватывающие фундаментальные основы Qt/C++, прикладную десктопную/мобильную разработку, embedded и IoT-направления, межсервисное взаимодействие, криптографию, и полноценный, систематический курс по SQL и реляционным базам данных.