С какими классическими моделями данных работала

Классические модели данных: Практический опыт

Рассказываю о моделях данных, с которыми работал в разных проектах. Каждая модель подходит для своих задач.

1. Star Schema (Звезда)

Самая популярная модель в data warehouse.

-- Fact таблица (события, транзакции)
CREATE TABLE fact_sales (
    sale_id BIGINT PRIMARY KEY,
    date_id INT,           -- FK к dimension
    product_id INT,        -- FK к dimension
    store_id INT,          -- FK к dimension
    customer_id INT,       -- FK к dimension
    amount DECIMAL(12,2),
    quantity INT
);

-- Dimension таблицы (справочники)
CREATE TABLE dim_date (
    date_id INT PRIMARY KEY,
    date DATE,
    year INT,
    month INT,
    day INT,
    is_weekend BOOLEAN
);

CREATE TABLE dim_product (
    product_id INT PRIMARY KEY,
    name VARCHAR(255),
    category VARCHAR(100),
    price DECIMAL(12,2)
);

CREATE TABLE dim_store (
    store_id INT PRIMARY KEY,
    name VARCHAR(255),
    city VARCHAR(100),
    region VARCHAR(100)
);

CREATE TABLE dim_customer (
    customer_id INT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255),
    segment VARCHAR(50)
);

Преимущества:

• Простые и быстрые запросы
• Эффективные индексы
• Легко добавлять новые измерения

Использование:

-- Простой аналитический запрос
SELECT 
    d.year,
    d.month,
    p.category,
    s.region,
    SUM(f.amount) as total_sales
FROM fact_sales f
JOIN dim_date d ON f.date_id = d.date_id
JOIN dim_product p ON f.product_id = p.product_id
JOIN dim_store s ON f.store_id = s.store_id
GROUP BY d.year, d.month, p.category, s.region;

Реальный кейс: Использовал для e-commerce analytics на 500GB data warehouse. Запросы выполнялись за секунды.

2. Snowflake Schema (Снежинка)

Оптимизация Star Schema для экономии памяти.

-- Вместо dim_product с категорией
-- Разбиваем на две таблицы

CREATE TABLE dim_product (
    product_id INT PRIMARY KEY,
    name VARCHAR(255),
    category_id INT
);

CREATE TABLE dim_category (
    category_id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50)
);

-- Запрос становится сложнее
SELECT 
    c.department,
    SUM(f.amount) as total_sales
FROM fact_sales f
JOIN dim_product p ON f.product_id = p.product_id
JOIN dim_category c ON p.category_id = c.category_id  -- Дополнительный JOIN
GROUP BY c.department;

Преимущества:

• Меньше памяти (нормализованные dimensions)
• Избегаем дублирования данных

Недостатки:

• Больше JOIN'ов = медленнее запросы
• Сложность

Когда использовал: Для очень больших dimension таблиц (10M+ строк категорий).

3. Fact Constellation (Galaxy)

Множество fact таблиц, разделяющих dimensions.

-- Несколько fact таблиц разных процессов
CREATE TABLE fact_sales (
    sale_id BIGINT PRIMARY KEY,
    date_id INT,
    product_id INT,
    amount DECIMAL(12,2)
);

CREATE TABLE fact_inventory (
    inventory_id BIGINT PRIMARY KEY,
    date_id INT,
    product_id INT,
    warehouse_id INT,
    quantity INT
);

CREATE TABLE fact_returns (
    return_id BIGINT PRIMARY KEY,
    date_id INT,
    product_id INT,
    amount DECIMAL(12,2)
);

-- Все используют dim_date, dim_product
CREATE TABLE dim_date (...);
CREATE TABLE dim_product (...);

Использование:

-- Анализ: продажи vs возвраты
SELECT 
    p.name,
    SUM(s.amount) as sales,
    SUM(r.amount) as returns,
    SUM(s.amount) - SUM(r.amount) as net_sales
FROM dim_product p
LEFT JOIN fact_sales s ON p.product_id = s.product_id
LEFT JOIN fact_returns r ON p.product_id = r.product_id
GROUP BY p.name;

Реальный кейс: Использовал для retail компании с 3 fact таблицами (sales, returns, exchanges).

4. Third Normal Form (3NF) - OLTP

Строго нормализованная для минимизации дублирования.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    unit_price DECIMAL(12,2),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(255),
    price DECIMAL(12,2)
);

Характеристики:

• Каждый факт хранится один раз
• Минимум аномалий при UPDATE/DELETE
• Много JOIN'ов в запросах

Использование:

-- Даже простой запрос требует множество JOIN'ов
SELECT 
    c.name,
    o.order_date,
    p.name as product,
    oi.quantity,
    oi.unit_price
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE c.customer_id = 123;

Когда использовал: Для OLTP систем (e-commerce приложения, банковские системы).

5. Денормализованная модель (OLAP)

Для аналитики: добавляем дублирование для скорости.

-- Вместо 5 таблиц с JOIN'ами
CREATE TABLE orders_denormalized (
    order_id INT,
    customer_id INT,
    customer_name VARCHAR(255),
    customer_email VARCHAR(255),
    order_date DATE,
    product_id INT,
    product_name VARCHAR(255),
    product_category VARCHAR(100),
    product_price DECIMAL(12,2),
    quantity INT,
    total_amount DECIMAL(12,2)
);

-- Запрос становится проще и быстрее
SELECT 
    customer_name,
    product_category,
    SUM(total_amount) as total_sales
FROM orders_denormalized
GROUP BY customer_name, product_category;

Характеристики:

• Дублирование данных
• Очень быстрые запросы (без JOIN'ов)
• UPDATE сложнее (обновить везде)

Когда использовал: Для большинства data warehouse (дешевле хранилище = больше денормализации).

6. Data Vault 2.0 (для Enterprise DW)

Хибрид OLTP и OLAP для больших систем.

-- Hub: уникальные сущности
CREATE TABLE hub_customer (
    customer_pk INT PRIMARY KEY,
    customer_id INT,
    load_date TIMESTAMP,
    record_source VARCHAR(100)
);

-- Link: отношения между сущностями
CREATE TABLE link_customer_order (
    link_pk INT PRIMARY KEY,
    customer_pk INT,
    order_pk INT,
    load_date TIMESTAMP,
    record_source VARCHAR(100)
);

-- Satellite: медленно меняющиеся данные
CREATE TABLE sat_customer_details (
    sat_pk INT PRIMARY KEY,
    customer_pk INT,
    customer_name VARCHAR(255),
    email VARCHAR(255),
    load_date TIMESTAMP,
    end_date TIMESTAMP,
    is_current BOOLEAN,
    record_source VARCHAR(100)
);

Характеристики:

• Полная история изменений
• Flexible structure для добавления новых источников
• Сложнее разобраться

Реальный кейс: Использовал для финансовой компании с множеством источников данных и аудитом.

7. Kimball vs Inmon подход

Kimball (Bottom-up):

• Star Schema, денормализованные
• Быстро для аналитиков
• Итеративная разработка

Inmon (Top-down):

• 3NF в ядре, денормализованные слои сверху
• Слож, но scalable
• Долгое планирование

Реальный опыт: Большинство modern DW использует Kimball из-за простоты.

8. Time Series данные

CREATE TABLE metrics (
    metric_id INT,
    timestamp TIMESTAMP,
    value FLOAT,
    tags JSONB
);

CREATE INDEX idx_metric_timestamp 
ON metrics(metric_id, timestamp DESC);

-- Типичные запросы
SELECT * FROM metrics 
WHERE metric_id = 1 
AND timestamp >= NOW() - INTERVAL '1 hour'
ORDER BY timestamp DESC;

Когда использовал: Для мониторинга, метрик приложения, IoT данных.

9. Документные данные (NoSQL)

# MongoDB пример
db.orders.insert_one({
    "_id": ObjectId(),
    "customer_id": 123,
    "items": [
        {"product_id": 1, "quantity": 2, "price": 50},
        {"product_id": 2, "quantity": 1, "price": 100}
    ],
    "total": 200,
    "status": "shipped"
})

# Гибкая структура, вложенные данные

Когда использовал: Для rapid prototyping, неструктурированных данных, микросервисов.

Рекомендации по выбору модели

Заключение

Выбор модели зависит от сценария: OLTP требует нормализации (3NF), OLAP требует денормализации (Star Schema). За 10+ лет я использовал все эти модели в разных проектах, и каждая имеет своё место.