Приведи пример когда приходилось проектировать базу данных

Пример проектирования базы данных для системы управления складом и заказами

В одном из проектов мне пришлось проектировать реляционную базу данных для системы управления складом, заказами и доставкой для e-commerce компании. Система должна была обрабатывать десятки тысяч товаров, сотни заказов ежедневно и интегрироваться с внешними сервисами доставки и платежей.

Анализ требований и бизнес-процессов

Перед проектированием я провел анализ с бизнес-аналитиками и стейкхолдерами, выделив ключевые сущности:

• Товары (каталог с категориями, атрибутами)
• Складские остатки (учет по партиям, FIFO/LIFO методы)
• Заказы (со статусами, платежами, доставкой)
• Пользователи (клиенты, менеджеры, курьеры)
• Отчеты (аналитика продаж, остатков)

Проектирование логической модели

Я создал ER-диаграмму в draw.io, определив основные таблицы и связи:

-- Основные таблицы (упрощенно)
CREATE TABLE Products (
    ProductId INT PRIMARY KEY IDENTITY(1,1),
    SKU NVARCHAR(50) UNIQUE NOT NULL,
    Name NVARCHAR(200) NOT NULL,
    CategoryId INT FOREIGN KEY REFERENCES Categories(CategoryId),
    Price DECIMAL(10,2) NOT NULL,
    Weight DECIMAL(8,3) NULL,
    IsActive BIT DEFAULT 1,
    CreatedAt DATETIME DEFAULT GETDATE(),
    INDEX IX_Products_Category (CategoryId),
    INDEX IX_Products_SKU (SKU)
);

CREATE TABLE Inventory (
    InventoryId BIGINT PRIMARY KEY IDENTITY(1,1),
    ProductId INT NOT NULL FOREIGN KEY REFERENCES Products(ProductId),
    WarehouseId INT NOT NULL FOREIGN KEY REFERENCES Warehouses(WarehouseId),
    BatchNumber NVARCHAR(100) NULL,
    Quantity INT NOT NULL DEFAULT 0,
    ReservedQuantity INT NOT NULL DEFAULT 0,
    ExpiryDate DATE NULL,
    ReceivedAt DATETIME DEFAULT GETDATE(),
    CONSTRAINT CK_NonNegativeQuantity CHECK (Quantity >= 0),
    CONSTRAINT CK_ValidReserved CHECK (ReservedQuantity <= Quantity),
    UNIQUE (ProductId, WarehouseId, BatchNumber)
);

CREATE TABLE Orders (
    OrderId BIGINT PRIMARY KEY IDENTITY(1,1),
    OrderNumber NVARCHAR(50) UNIQUE NOT NULL,
    CustomerId INT NOT NULL FOREIGN KEY REFERENCES Customers(CustomerId),
    StatusId INT NOT NULL FOREIGN KEY REFERENCES OrderStatuses(StatusId),
    TotalAmount DECIMAL(12,2) NOT NULL,
    CreatedAt DATETIME DEFAULT GETDATE(),
    UpdatedAt DATETIME DEFAULT GETDATE(),
    INDEX IX_Orders_Customer (CustomerId),
    INDEX IX_Orders_Status (StatusId),
    INDEX IX_Orders_Created (CreatedAt)
);

CREATE TABLE OrderItems (
    OrderItemId BIGINT PRIMARY KEY IDENTITY(1,1),
    OrderId BIGINT NOT NULL FOREIGN KEY REFERENCES Orders(OrderId),
    ProductId INT NOT NULL FOREIGN KEY REFERENCES Products(ProductId),
    Quantity INT NOT NULL CHECK (Quantity > 0),
    UnitPrice DECIMAL(10,2) NOT NULL,
    Discount DECIMAL(10,2) DEFAULT 0,
    INDEX IX_OrderItems_Product (ProductId),
    INDEX IX_OrderItems_Order (OrderId)
);

Ключевые архитектурные решения

1. Нормализация и денормализация

   • Основные сущности нормализованы до 3NF для избежания аномалий
   • Для отчетов добавил денормализованные представления с агрегированными данными
   • Исторические таблицы для аудита изменений цен и остатков

2. Индексы и производительность

   • Составные индексы для частых запросов: (ProductId, WarehouseId)
   • Индексы покрытия для часто запрашиваемых полей
   • Партиционирование таблицы OrderItems по дате для архивных данных

3. Транзакционная целостность

   // Пример бизнес-логики резервирования товара
   using (var transaction = context.Database.BeginTransaction(
       System.Data.IsolationLevel.Serializable))
   {
       try
       {
           // Проверка доступного количества
           var available = context.Inventory
               .Where(i => i.ProductId == productId && 
                          i.WarehouseId == warehouseId &&
                          i.Quantity - i.ReservedQuantity >= quantity)
               .Sum(i => i.Quantity - i.ReservedQuantity);
           
           if (available < quantity)
               throw new InsufficientStockException();
           
           // Резервирование по методу FIFO
           var batches = context.Inventory
               .Where(i => i.ProductId == productId && 
                          i.WarehouseId == warehouseId &&
                          i.Quantity > i.ReservedQuantity)
               .OrderBy(i => i.ReceivedAt)
               .ToList();
           
           foreach (var batch in batches)
           {
               var toReserve = Math.Min(quantity, 
                   batch.Quantity - batch.ReservedQuantity);
               batch.ReservedQuantity += toReserve;
               quantity -= toReserve;
               
               if (quantity == 0) break;
           }
           
           context.SaveChanges();
           transaction.Commit();
       }
       catch
       {
           transaction.Rollback();
           throw;
       }
   }
   

4. Миграции и версионирование

   • Использовал Entity Framework Core Migrations для управления схемой
   • Скрипты отката для каждой миграции
   • Сиды для справочных данных (статусы заказов, роли)

Проблемы и решения

Проблема 1: Конкурентное обновление остатков при flash-распродажах
Решение: Использовал оптимистическую блокировку с RowVersion и retry-логикой:

public class Inventory
{
    public int InventoryId { get; set; }
    public int ProductId { get; set; }
    public int Quantity { get; set; }
    [Timestamp]
    public byte[] RowVersion { get; set; }
}

// В логике с retry-политикой
var policy = Policy.Handle<DbUpdateConcurrencyException>()
    .WaitAndRetryAsync(3, retryAttempt => 
        TimeSpan.FromMilliseconds(100 * retryAttempt));

Проблема 2: Медленные отчеты по историческим данным
Решение: Реализовал агрегирующую таблицу с ежедневным обновлением через SQL Agent Job:

CREATE TABLE DailySalesAggregate (
    Date DATE PRIMARY KEY,
    ProductId INT,
    TotalSold INT,
    TotalRevenue DECIMAL(14,2),
    FOREIGN KEY (ProductId) REFERENCES Products(ProductId),
    INDEX IX_DailySales_Product (ProductId)
);

-- Обновление по расписанию
MERGE DailySalesAggregate AS target
USING (
    SELECT 
        CAST(o.CreatedAt AS DATE) AS Date,
        oi.ProductId,
        SUM(oi.Quantity) AS TotalSold,
        SUM(oi.Quantity * oi.UnitPrice) AS TotalRevenue
    FROM OrderItems oi
    JOIN Orders o ON oi.OrderId = o.OrderId
    WHERE o.CreatedAt >= DATEADD(DAY, -1, GETDATE())
    GROUP BY CAST(o.CreatedAt AS DATE), oi.ProductId
) AS source ON target.Date = source.Date AND target.ProductId = source.ProductId
WHEN MATCHED THEN UPDATE SET 
    TotalSold = source.TotalSold,
    TotalRevenue = source.TotalRevenue
WHEN NOT MATCHED THEN INSERT (Date, ProductId, TotalSold, TotalRevenue)
    VALUES (source.Date, source.ProductId, source.TotalSold, source.TotalRevenue);

Итоги и результаты

Проектирование заняло 3 недели (анализ + прототип + согласование). В результате:

• Производительность: 95% запросов выполнялись < 100 мс
• Масштабируемость: Возможность горизонтального шардинга по складам
• Надежность: ACID-транзакции для финансовых операций
• Гибкость: Легкое добавление новых типов товаров через EAV-паттерн для атрибутов

Важный урок: Необходимо закладывать инструменты мониторинга с самого начала (запросы к pg_stat_statements в PostgreSQL или Query Store в SQL Server) для выявления узких мест в реальной нагрузке. Также критически важно согласовывать стратегии бэкапов и планы аварийного восстановления на этапе проектирования схемы.