Приведи пример, когда проектировал структуру БД

Приведи пример, когда проектировал структуру БД

Рассмотрим реальный пример проектирования БД для платформы интернет-магазина с подписками.

Контекст и требования

Создаём платформу e-commerce с поддержкой:

• Каталога товаров
• Заказов и платежей
• Подписок
• Уведомлений пользователям
• История действий

Шаг 1: Анализ требований

Функциональность:

• Пользователи могут создавать заказы
• Товары могут быть в категориях
• Поддерживаются подписки (месячная, годовая)
• Нужна история всех платежей
• Заказ может содержать несколько товаров
• Нужны скидки и промокоды

Нагрузка:

• 1M пользователей
• 100k товаров
• 10k заказов в день
• Высокая частота чтения каталога

Шаг 2: Проектирование схемы

-- Таблица пользователей
CREATE TABLE users (
    id UUID PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(255),
    phone VARCHAR(20),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL,
    deleted_at TIMESTAMP WITH TIME ZONE
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);

-- Таблица категорий товаров
CREATE TABLE categories (
    id UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    description TEXT,
    icon_url VARCHAR(500),
    parent_id UUID REFERENCES categories(id) ON DELETE CASCADE,
    display_order INT,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE INDEX idx_categories_slug ON categories(slug);
CREATE INDEX idx_categories_parent_id ON categories(parent_id);

-- Таблица товаров (с денормализацией для производительности)
CREATE TABLE products (
    id UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    sku VARCHAR(100) UNIQUE NOT NULL,
    category_id UUID NOT NULL REFERENCES categories(id),
    price_cents BIGINT NOT NULL,  -- Используем целые числа (копейки)
    cost_cents BIGINT,
    description TEXT,
    image_url VARCHAR(500),
    stock_quantity INT NOT NULL DEFAULT 0,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE INDEX idx_products_category_id ON products(category_id);
CREATE INDEX idx_products_sku ON products(sku);
CREATE INDEX idx_products_is_active ON products(is_active);

-- Таблица заказов
CREATE TABLE orders (
    id UUID PRIMARY KEY,
    order_number VARCHAR(50) UNIQUE NOT NULL,  -- Номер для пользователя
    user_id UUID NOT NULL REFERENCES users(id),
    total_price_cents BIGINT NOT NULL,
    discount_cents BIGINT DEFAULT 0,
    tax_cents BIGINT DEFAULT 0,
    status VARCHAR(50) NOT NULL,  -- pending, confirmed, shipped, delivered, cancelled
    payment_status VARCHAR(50) NOT NULL,  -- unpaid, paid, refunded
    delivery_address TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at);
CREATE INDEX idx_orders_order_number ON orders(order_number);

-- Таблица деталей заказа (Order Items)
CREATE TABLE order_items (
    id UUID PRIMARY KEY,
    order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id UUID NOT NULL REFERENCES products(id),
    quantity INT NOT NULL,
    unit_price_cents BIGINT NOT NULL,  -- Цена на момент заказа
    total_price_cents BIGINT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

-- Таблица платежей
CREATE TABLE payments (
    id UUID PRIMARY KEY,
    order_id UUID NOT NULL REFERENCES orders(id),
    payment_method VARCHAR(50) NOT NULL,  -- card, paypal, yandex_kassa
    amount_cents BIGINT NOT NULL,
    status VARCHAR(50) NOT NULL,  -- pending, success, failed, refunded
    transaction_id VARCHAR(255),  -- ID от платёжного шлюза
    error_message TEXT,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE INDEX idx_payments_order_id ON payments(order_id);
CREATE INDEX idx_payments_status ON payments(status);
CREATE INDEX idx_payments_created_at ON payments(created_at);

-- Таблица подписок
CREATE TABLE subscriptions (
    id UUID PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    plan_type VARCHAR(50) NOT NULL,  -- basic, premium, enterprise
    price_cents BIGINT NOT NULL,
    billing_period VARCHAR(50) NOT NULL,  -- monthly, yearly
    start_date TIMESTAMP WITH TIME ZONE NOT NULL,
    end_date TIMESTAMP WITH TIME ZONE NOT NULL,
    auto_renew BOOLEAN DEFAULT true,
    status VARCHAR(50) NOT NULL,  -- active, paused, cancelled
    created_at TIMESTAMP WITH TIME ZONE NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE INDEX idx_subscriptions_user_id ON subscriptions(user_id);
CREATE INDEX idx_subscriptions_status ON subscriptions(status);
CREATE INDEX idx_subscriptions_end_date ON subscriptions(end_date);

-- Таблица промокодов
CREATE TABLE promo_codes (
    id UUID PRIMARY KEY,
    code VARCHAR(50) UNIQUE NOT NULL,
    discount_percent DECIMAL(5, 2),
    discount_fixed_cents BIGINT,
    max_uses INT,
    current_uses INT DEFAULT 0,
    valid_from TIMESTAMP WITH TIME ZONE NOT NULL,
    valid_to TIMESTAMP WITH TIME ZONE NOT NULL,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE INDEX idx_promo_codes_code ON promo_codes(code);
CREATE INDEX idx_promo_codes_valid_to ON promo_codes(valid_to);

-- Таблица истории (для аудита)
CREATE TABLE audit_logs (
    id UUID PRIMARY KEY,
    entity_type VARCHAR(100) NOT NULL,  -- order, payment, user
    entity_id UUID NOT NULL,
    action VARCHAR(50) NOT NULL,  -- created, updated, deleted
    old_values JSONB,  -- Предыдущие значения
    new_values JSONB,  -- Новые значения
    user_id UUID REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE INDEX idx_audit_logs_entity ON audit_logs(entity_type, entity_id);
CREATE INDEX idx_audit_logs_created_at ON audit_logs(created_at);

Шаг 3: Ключевые решения и обоснование

1. UUID вместо auto-increment

// Преимущества:
// - Не раскрываем внутренние ID клиентам
// - Можно генерировать на клиенте (offline-first)
// - Легче при распределённых системах
// - Безопаснее

2. Денормализация (total_price в заказах)

-- Берём total из order_items (денормализовано)
SELECT total_price_cents FROM orders WHERE id = ?;
-- Вместо:
SELECT SUM(total_price_cents) FROM order_items WHERE order_id = ?;

3. Целые числа вместо DECIMAL для денег

// ❌ Плохо (precision issues)
DECIMAL(10, 2) price

// ✅ Хорошо (целые числа, в копейках)
BIGINT price_cents

public class Money {
    private long cents;
    
    public Money(String rubles, String kopiykas) {
        this.cents = Long.parseLong(rubles) * 100 + Long.parseLong(kopiykas);
    }
    
    public String toRubles() {
        return (cents / 100) + "." + (cents % 100);
    }
}

4. Мягкое удаление (soft delete)

-- Вместо DELETE, используем deleted_at
ALTER TABLE users ADD deleted_at TIMESTAMP WITH TIME ZONE;

-- При выборе:
SELECT * FROM users WHERE deleted_at IS NULL;

5. История действий (JSONB)

-- Сохраняем полную историю изменений
INSERT INTO audit_logs (entity_type, entity_id, action, old_values, new_values)
VALUES ('order', '123e4567', 'updated',
  jsonb_build_object('status', 'pending', 'total_price_cents', 10000),
  jsonb_build_object('status', 'confirmed', 'total_price_cents', 10000));

Шаг 4: Индексы (critical для производительности)

-- Поиск по пользователю (частая операция)
CREATE INDEX idx_orders_user_id_created_at ON orders(user_id, created_at DESC);

-- Поиск активных подписок
CREATE INDEX idx_subscriptions_status_end_date ON subscriptions(status, end_date);

-- Аналитика по датам
CREATE INDEX idx_orders_created_at_status ON orders(created_at, status);

Шаг 5: ORM маппинг (Hibernate/JPA)

@Entity
@Table(name = "orders")
public class Order {
    @Id
    private UUID id;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id", nullable = false)
    private User user;
    
    @OneToMany(mappedBy = "order", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<OrderItem> items = new ArrayList<>();
    
    @OneToMany(mappedBy = "order", cascade = CascadeType.ALL)
    private List<Payment> payments = new ArrayList<>();
    
    private Long totalPriceCents;
    private Long discountCents;
    
    @Enumerated(EnumType.STRING)
    private OrderStatus status;
    
    @CreationTimestamp
    @Column(nullable = false, updatable = false)
    private LocalDateTime createdAt;
    
    @UpdateTimestamp
    private LocalDateTime updatedAt;
}

@Entity
@Table(name = "order_items")
public class OrderItem {
    @Id
    private UUID id;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "order_id", nullable = false)
    private Order order;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "product_id", nullable = false)
    private Product product;
    
    private Integer quantity;
    private Long unitPriceCents;
}

Ключевые моменты моего проектирования

• Нормализация vs Денормализация — денормализовал total в orders для скорости
• Индексы — добавил индексы для частых запросов по user_id и created_at
• JSONB для истории — позволяет гибко хранить изменения
• UUID вместо int — безопаснее и удобнее
• Целые числа для денег — избегаем ошибок точности
• Soft delete — сохраняем данные для аудита
• Связи в ORM — использую LAZY loading, чтобы не N+1