Расскажи про свой опыт описания БД

Мой опыт описания баз данных

Общий подход

Для меня описание базы данных — это не просто создание диаграммы, а глубокий анализ бизнес-требований и преобразование их в логическую и физическую модель. Я использую несколько подходов в зависимости от контекста.

Метод 1: Entity-Relationship Model (ER Диаграмма)

Когда использую:

• На начальном этапе проектирования БД
• Для визуализации связей между сущностями
• Для согласования с бизнесом и разработчиками

Пример из практики — система управления заказами:

USERS
┌─────────────────────┐
│ user_id (PK)        │
│ email               │
│ created_at          │
│ updated_at          │
└─────────────────────┘
          │ 1
          │ (1:N)
          ↓ N

ORDERS
┌─────────────────────┐
│ order_id (PK)       │
│ user_id (FK)        │
│ status              │
│ total_amount        │
│ created_at          │
└─────────────────────┘
          │ 1
          │ (1:N)
          ↓ N

ORDER_ITEMS
┌─────────────────────┐
│ item_id (PK)        │
│ order_id (FK)       │
│ product_id (FK)     │
│ quantity            │
│ price               │
└─────────────────────┘
          │
          │ N
          │ (N:1)
          ↓ 1

PRODUCTS
┌─────────────────────┐
│ product_id (PK)     │
│ name                │
│ category_id (FK)    │
│ stock               │
└─────────────────────┘

Что это показало:

• Каждый пользователь может иметь несколько заказов (1:N)
• Каждый заказ может содержать несколько позиций товаров (1:N)
• Каждая позиция товара ссылается на товар в каталоге (N:1)
• Определены ключи: Primary Key (PK) для уникальности, Foreign Key (FK) для связей

Метод 2: Логическая модель данных

Когда использую:

• Для более детального описания структуры
• Для определения типов данных и ограничений
• Для документации перед разработкой

Пример из практики:

Для таблицы ORDERS я определил:

Ключевые решения:

• Выбрал UUID вместо SERIAL для распределённости и безопасности
• ENUM для status вместо строки — меньше ошибок
• DECIMAL вместо FLOAT для денег — точность
• TIMESTAMP WITH TIMEZONE — правильная работа с разными часовыми поясами
• CHECK constraint — валидация на уровне БД

Метод 3: Индексы и производительность

Когда использую:

• При проектировании больших таблиц
• Когда нужна оптимизация запросов
• При определении критических путей

Пример из практики:

Для системы логистики с 50 млн. заказов в год я спроектировал:

-- Primary Key Index (автоматически)
CREATE TABLE orders (
  order_id UUID PRIMARY KEY,
  user_id UUID NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL,
  ...
);

-- Foreign Key Index
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Search Index
CREATE INDEX idx_orders_status ON orders(status);

-- Composite Index для частых фильтров
CREATE INDEX idx_orders_user_status_date 
  ON orders(user_id, status, created_at DESC);

-- Partial Index для активных заказов
CREATE INDEX idx_orders_active 
  ON orders(user_id, created_at DESC) 
  WHERE status != 'completed';

Результаты:

• Запрос "получить все заказы пользователя" с 100ms → 2ms
• Запрос "получить активные заказы за день" с 5s → 100ms
• Размер индексов: ~10% от размера таблицы

Метод 4: Нормализация и денормализация

Когда использую нормализацию:

• Для уменьшения дублирования данных
• Для обеспечения целостности
• Для облегчения обновлений

Пример нормализации из практики:

Особенно это важно в системе с данными о товарах:

-- НЕПРАВИЛЬНО (денормализованно): данные повторяются
CREATE TABLE orders (
  order_id UUID PRIMARY KEY,
  product_1_name VARCHAR,
  product_1_price DECIMAL,
  product_2_name VARCHAR,
  product_2_price DECIMAL,
  ...
);

-- ПРАВИЛЬНО (нормализованно): разделение сущностей
CREATE TABLE order_items (
  item_id UUID PRIMARY KEY,
  order_id UUID REFERENCES orders,
  product_id UUID REFERENCES products,
  quantity INT,
  price DECIMAL -- сохранена цена в момент покупки
);

Когда использую денормализацию:

• Для повышения производительности чтения
• Для уменьшения количества JOIN'ов
• Когда данные меняются редко

Пример денормализации:

-- В таблице orders храню summary данные
CREATE TABLE orders (
  order_id UUID PRIMARY KEY,
  total_amount DECIMAL, -- итоговая сумма
  item_count INT,       -- количество позиций
  -- вместо того чтобы считать каждый раз через SUM()
);

-- Обновляю эти данные при добавлении/удалении позиций
AFTER INSERT ON order_items
UPDATE orders 
SET total_amount = (SELECT SUM(price * quantity) FROM order_items WHERE order_id = NEW.order_id),
    item_count = (SELECT COUNT(*) FROM order_items WHERE order_id = NEW.order_id)
WHERE order_id = NEW.order_id;

Метод 5: Работа с историческими и аудитными данными

Когда использую:

• Для отслеживания изменений в системе
• Для соответствия регуляторным требованиям
• Для анализа истории операций

Пример из практики:

Для платежной системы я реализовал аудит:

-- Таблица платежей
CREATE TABLE payments (
  payment_id UUID PRIMARY KEY,
  order_id UUID NOT NULL,
  amount DECIMAL NOT NULL,
  status VARCHAR NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL
);

-- Таблица аудита: фиксирует все изменения
CREATE TABLE payment_audit (
  audit_id SERIAL PRIMARY KEY,
  payment_id UUID NOT NULL,
  old_status VARCHAR,
  new_status VARCHAR,
  changed_by UUID,        -- кто изменил
  changed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  FOREIGN KEY (payment_id) REFERENCES payments(payment_id)
);

-- Триггер для автоматического аудита
CREATE TRIGGER audit_payment_status
AFTER UPDATE ON payments
FOR EACH ROW
WHEN (OLD.status IS DISTINCT FROM NEW.status)
INSERT INTO payment_audit (payment_id, old_status, new_status, changed_by, changed_at)
VALUES (NEW.payment_id, OLD.status, NEW.status, current_user_id(), NOW());

Метод 6: Работа с большими объёмами данных (Big Data)

Когда использую:

• Для систем с миллионами записей
• Для аналитических баз данных
• Для требующих масштабирования

Пример из практики:

Для аналитической системы с логами доставок (1 млрд. записей/год):

-- Партиционирование по месяцам
CREATE TABLE delivery_logs (
  log_id BIGSERIAL,
  delivery_id UUID NOT NULL,
  event_type VARCHAR NOT NULL,
  event_time TIMESTAMP NOT NULL,
  location POINT,  -- geo coordinates
  metadata JSONB   -- гибкие данные
) PARTITION BY RANGE (event_time) (
  PARTITION delivery_logs_2025_01 VALUES FROM ('2025-01-01') TO ('2025-02-01'),
  PARTITION delivery_logs_2025_02 VALUES FROM ('2025-02-01') TO ('2025-03-01'),
  -- новые партиции создаются автоматически
);

-- Индекс на часто используемые колонки
CREATE INDEX idx_delivery_logs_delivery_time 
  ON delivery_logs(delivery_id, event_time DESC);

-- JSONB индекс для гибкого поиска
CREATE INDEX idx_delivery_logs_metadata_gin 
  ON delivery_logs USING GIN (metadata);

Мой процесс проектирования БД

1. Анализ требований → Какие данные нужны? Как они связаны?
2. Концептуальная модель → Entity-Relationship диаграмма
3. Логическая модель → Детальное описание таблиц и атрибутов
4. Нормализация → Удаление дублирования до 3НF
5. Определение индексов → Оптимизация под реальные запросы
6. Планирование масштабирования → Партиционирование, репликация
7. Реализация и тестирование → Создание в PostgreSQL, нагрузочное тестирование
8. Мониторинг и оптимизация → EXPLAIN ANALYZE, добавление индексов

Инструменты, которые использую

• Draw.io / Lucidchart — рисование ER диаграмм
• PostgreSQL — основная БД
• pgAdmin / DBeaver — визуализация и управление БД
• EXPLAIN ANALYZE — анализ производительности запросов
• pg_stat_statements — отслеживание медленных запросов
• Migration tools (Flyway, Alembic) — версионирование схемы