На что обращал внимание при создании таблиц в БД?

Ключевые аспекты проектирования таблиц в БД

При проектировании таблиц в реляционных базах данных я уделяю внимание нескольким фундаментальным аспектам, которые напрямую влияют на производительность, масштабируемость и поддержку приложения. Вот основные принципы, которым я следую:

1. Нормализация структуры данных

Первичная задача — нормализация до 3NF (третьей нормальной формы), чтобы избежать аномалий при вставке, обновлении и удалении данных. При этом я оцениваю компромиссы: избыточная нормализация может привести к сложным JOIN-запросам, поэтому иногда осознанно допускаю денормализацию для частых read-операций.

-- Пример нормализованной структуры
CREATE TABLE Users (
    UserId INT PRIMARY KEY IDENTITY,
    Email NVARCHAR(255) UNIQUE NOT NULL,
    CreatedAt DATETIME2 DEFAULT GETDATE()
);

CREATE TABLE UserProfiles (
    ProfileId INT PRIMARY KEY IDENTITY,
    UserId INT UNIQUE NOT NULL FOREIGN KEY REFERENCES Users(UserId),
    FullName NVARCHAR(200) NOT NULL,
    -- Другие поля профиля
);

2. Выбор подходящих типов данных и ограничений

• Точность типов: INT vs BIGINT, VARCHAR(n) с разумным лимитом вместо TEXT для строк
• Ограничения NOT NULL для обязательных полей, что улучшает производительность и ясность схемы
• CHECK-constraints для валидации доменных значений
• DEFAULT-значения для полей с предсказуемым содержимым

CREATE TABLE Orders (
    OrderId INT PRIMARY KEY IDENTITY,
    Status TINYINT NOT NULL CHECK (Status BETWEEN 1 AND 5),
    Amount DECIMAL(10, 2) NOT NULL CHECK (Amount >= 0),
    CreatedAt DATETIME2 NOT NULL DEFAULT GETDATE(),
    -- Поле с явным ограничением формата
    OrderCode VARCHAR(10) NOT NULL CHECK (OrderCode LIKE '[A-Z][A-Z]-[0-9][0-9][0-9][0-9]')
);

3. Эффективная система индексов

• Первичный ключ — минимальный и неизменяемый (предпочтительно INT или BIGINT)
• Кластеризованный индекс — тщательный выбор, так как он определяет физическое расположение данных
• Некластеризованные индексы на поля, используемые в WHERE, JOIN и ORDER BY
• Индексы на внешние ключи для ускорения JOIN-операций
• Covering indexes для частых запросов, возвращающих подмножества полей

-- Стратегическое создание индексов
CREATE NONCLUSTERED INDEX IX_Orders_Status_Created 
    ON Orders(Status, CreatedAt DESC)
    INCLUDE (Amount, CustomerId); -- Covering index

4. Внешние ключи и референциальная целостность

Обязательное использование FOREIGN KEY constraints для поддержания целостности данных, с явным указанием ON DELETE и ON UPDATE действий:

CREATE TABLE OrderItems (
    ItemId INT PRIMARY KEY IDENTITY,
    OrderId INT NOT NULL,
    ProductId INT NOT NULL,
    FOREIGN KEY (OrderId) REFERENCES Orders(OrderId) 
        ON DELETE CASCADE, -- Удаляем позиции при удалении заказа
    FOREIGN KEY (ProductId) REFERENCES Products(ProductId)
        ON UPDATE CASCADE -- Обновляем ID при изменении в Products
);

5. Проектирование для масштабирования и производительности

• Партиционирование крупных таблиц (по датам, регионам)
• Версионность для исторических данных (темпоральные таблицы в SQL Server)
• Columnstore индексы для аналитических нагрузок
• Вычисляемые поля для денормализации с индексацией, где это уместно

-- Пример темпоральной таблицы
CREATE TABLE EmployeeHistory (
    EmployeeId INT NOT NULL,
    Position NVARCHAR(100) NOT NULL,
    Salary DECIMAL(10,2) NOT NULL,
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
) WITH (SYSTEM_VERSIONING = ON);

6. Безопасность и управление доступом

• Минимальные привилегии для прикладных пользователей
• Шифрование конфиденциальных данных (Always Encrypted в SQL Server)
• Маскирование данных для разработчиков и тестировщиков
• Аудит изменений через триггеры или встроенные механизмы СУБД

7. Соглашения об именовании и документация

• Единый стиль именования (PascalCase или snake_case)
• Префиксы для индексов, ограничений (PK_, FK_, IX_)
• Комментарии к таблицам и сложным полям
• Скрипты миграций для всех изменений схемы

8. Факторы, специфичные для предметной области

• JSON/XML поля в современных СУБД для полуструктурированных данных
• Полнотекстовый поиск для текстовых полей
• Геопространственные типы для локационных данных
• Оптимистическая блокировка через поля версий для высоконагруженных систем

Баланс между теорией и практикой

Ключевой принцип — осознанный компромисс между нормализацией и производительностью. Например, в OLTP-системах я строже следую нормализации, в аналитических (OLAP) — допускаю денормализацию. Современные СУБД (PostgreSQL, SQL Server) предлагают гибридные подходы, такие как индексируемые JSON-поля, что позволяет сочетать структурированность с гибкостью.

Проектирование таблиц — это итеративный процесс, где я постоянно пересматриваю схему по мере роста приложения, анализируя планы запросов и статистику использования. Инструменты вроде Execution Plan в SQL Server или EXPLAIN ANALYZE в PostgreSQL незаменимы для валидации решений по индексации и структурированию данных.