Как спроектировать базу данных для парковочных зон в Москве с нуля?

Проектирование БД для парковочных зон Москвы требует учета географических данных, тарификации, реального времени занятости и интеграции с городскими системами. Вот как я подходил бы к такому проекту:

1. Концептуальная модель

Парковочная зона (район) → Парковочные места → Транзакции
       ↑                           ↑               ↑
Тарифные планы              Датчики занятости   Пользователи

2. Физическая модель (PostgreSQL с PostGIS)

-- Расширение для работы с геоданными
CREATE EXTENSION IF NOT EXISTS postgis;

-- Таблица парковочных зон (административные районы)
CREATE TABLE parking_zones (
    zone_id SERIAL PRIMARY KEY,
    zone_number VARCHAR(10) UNIQUE NOT NULL,  -- Номер зоны (например, "101")
    zone_name VARCHAR(100) NOT NULL,          -- Название ("ЦАО, Тверской район")
    district VARCHAR(50),                     -- Административный округ

    -- Геометрия зоны (полигон границ)
    geometry GEOGRAPHY(POLYGON, 4326) NOT NULL,

    -- Характеристики
    total_capacity INTEGER NOT NULL CHECK (total_capacity > 0),
    disabled_spots INTEGER DEFAULT 0,
    electric_spots INTEGER DEFAULT 0,         -- Места для электромобилей

    -- Метаданные
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Пространственный индекс для быстрого поиска по координатам
CREATE INDEX idx_parking_zones_geometry ON parking_zones USING GIST(geometry);

-- Таблица парковочных мест
CREATE TABLE parking_spots (
    spot_id SERIAL PRIMARY KEY,
    zone_id INTEGER NOT NULL REFERENCES parking_zones(zone_id) ON DELETE CASCADE,
    spot_number VARCHAR(20) NOT NULL,          -- Уникальный номер в зоне

    -- Геометрия места (точка)
    coordinates GEOGRAPHY(POINT, 4326) NOT NULL,

    -- Характеристики места
    spot_type VARCHAR(20) DEFAULT 'standard' CHECK (
        spot_type IN ('standard', 'disabled', 'electric', 'loading', 'taxi')
    ),
    is_operational BOOLEAN DEFAULT TRUE,      -- Техническая исправность

    -- Временные метки
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(zone_id, spot_number)
);

-- Таблица тарифов (сложная тарификация по времени и типам)
CREATE TABLE tariff_plans (
    tariff_id SERIAL PRIMARY KEY,
    zone_id INTEGER REFERENCES parking_zones(zone_id),
    vehicle_type VARCHAR(20) DEFAULT 'passenger' CHECK (
        vehicle_type IN ('passenger', 'commercial', 'motorcycle', 'electric')
    ),

    -- Тариф по периодам (в копейках)
    first_hour_price INTEGER NOT NULL,        -- Первый час
    second_hour_price INTEGER,                -- Второй час
    subsequent_hour_price INTEGER,            -- Последующие часы
    daily_max_price INTEGER,                  -- Максимум в сутки

    -- Временные ограничения
    valid_from TIME,                          -- Начало действия (например, 08:00)
    valid_to TIME,                            -- Окончание (например, 21:00)
    is_weekend_applicable BOOLEAN DEFAULT TRUE,

    effective_date DATE NOT NULL,
    expiration_date DATE
);

-- Таблица занятости в реальном времени (партиционированная)
CREATE TABLE spot_occupancy (
    occupancy_id BIGSERIAL,
    spot_id INTEGER NOT NULL REFERENCES parking_spots(spot_id),
    is_occupied BOOLEAN NOT NULL,
    detected_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    source VARCHAR(20) DEFAULT 'sensor' CHECK (
        source IN ('sensor', 'manual', 'payment_system')
    )
) PARTITION BY RANGE (detected_at);

-- Создание партиций по месяцам
CREATE TABLE spot_occupancy_2024_03 PARTITION OF spot_occupancy
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

-- Таблица транзакций
CREATE TABLE parking_transactions (
    transaction_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    spot_id INTEGER NOT NULL REFERENCES parking_spots(spot_id),
    vehicle_plate VARCHAR(20),                -- Госномер (если используется)
    user_id INTEGER,                          -- ID в системе (если регистрация)

    -- Временные метки
    arrival_time TIMESTAMP WITH TIME ZONE NOT NULL,
    departure_time TIMESTAMP WITH TIME ZONE,

    -- Финансовые данные
    tariff_applied INTEGER REFERENCES tariff_plans(tariff_id),
    total_amount DECIMAL(10, 2),              -- Сумма к оплате
    amount_paid DECIMAL(10, 2),               -- Фактически оплачено
    payment_method VARCHAR(20),               -- 'cash', 'card', 'mobile', 'subscription'
    payment_status VARCHAR(20) DEFAULT 'pending'
);

-- Индексы для производительности
CREATE INDEX idx_transactions_spot_time ON parking_transactions(spot_id, arrival_time);
CREATE INDEX idx_occupancy_spot_time ON spot_occupancy(spot_id, detected_at DESC);

3. Оптимизации и архитектурные решения

-- Материализованное представление для статистики занятости
CREATE MATERIALIZED VIEW mv_zone_occupancy_stats AS
SELECT 
    z.zone_id,
    z.zone_name,
    COUNT(DISTINCT s.spot_id) as total_spots,
    COUNT(DISTINCT CASE WHEN o.is_occupied THEN s.spot_id END) as occupied_spots,
    ROUND(
        COUNT(DISTINCT CASE WHEN o.is_occupied THEN s.spot_id END) * 100.0 / 
        COUNT(DISTINCT s.spot_id), 2
    ) as occupancy_rate,
    MAX(o.detected_at) as last_update
FROM parking_zones z
JOIN parking_spots s ON z.zone_id = s.zone_id
LEFT JOIN spot_occupancy o ON s.spot_id = o.spot_id 
    AND o.detected_at >= NOW() - INTERVAL '5 minutes'
WHERE z.is_active = TRUE AND s.is_operational = TRUE
GROUP BY z.zone_id, z.zone_name
WITH DATA;

-- Обновление каждые 5 минут
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_zone_occupancy_stats;

4. Интеграция и масштабирование

• Redis для кэширования текущей занятости и быстрого API
• TimescaleDB (расширение PostgreSQL) для временных рядов данных с датчиков
• Kafka для потоковой обработки событий с датчиков
• Elasticsearch для полнотекстового поиска парковок по адресу
• API Gateway для интеграции с мобильными приложениями и городскими системами

5. Мониторинг и аналитика

• Дашборды в Grafana для отслеживания занятости в реальном времени
• ETL-пайплайны для загрузки данных в DWH (витрины для аналитиков)
• ML-модели для прогнозирования занятости по времени суток и дням недели