Привет, многоуважаемые читатели Хабра!

Как нынешний аналитик БД с прошлым пятилетним опытом в B2B и B2C сегментах телеком-провайдеров, я хотел бы осветить одну из "болей" абонентов - «архивные» тарифы. Абоненты годами сидят на устаревших и дорогих тарифах, не зная, что в их же доме уже давно доступны более выгодные предложения от конкурентов.

Чтобы решить эту проблему и обеспечить открытость рынка тарифов, я решил объединить свой опыт в телекоме с навыками программирования. Так родилась идея проекта по автоматизированному парсингу тарифов провайдеров.

В первой части цикла статей я расскажу об архитектуре базы данных, которую я спроектировал для этой задачи.

Зачем это нужно?

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

Архитектура БД: схема «Звезда» на PostgreSQL

Проектирование я начал со структуры на PostgreSQL, используя схему «Звезда».

• Таблица фактов: Таблица город_провайдер со связью города с провайдером.

• Таблицы измерений: Таблицы с информацией о тарифах (тарифы), городах (города) и провайдерах (провайдеры).

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

CONSTRAINT ограничение_для_pk_id PRIMARY KEY (id),
CONSTRAINT ограничение_для_fk_id FOREIGN KEY (id) REFERENCES (id),
CONSTRAINT uq_город_провайдер UNIQUE (город_id, провайдер_id) -- уникальность значений для таблицы связей

Управление историчностью и автоматизация

Чтобы отслеживать изменения данных (например, когда провайдер деактивирует старый тариф), в таблицах связей, провайдерах и тарифах ведется историчность изменений с помощью автоматических триггеров.

Каждая сущность (кроме городов) имеет временные метки start_at и end_at для отслеживания актуальности записи.

CREATE TABLE IF NOT EXISTS сущности (
 -- ...
 start_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- для сущности связи
 end_at TIMESTAMP DEFAULT NULL, -- NULL значит «действует до сих пор»
 is_active BOOLEAN DEFAULT TRUE
);

Функции автоматически управляют этими полями при обновлениях:

CREATE OR REPLACE FUNCTION функция_реагирующая_на_изменения()
RETURNS TRIGGER AS $$
BEGIN
-- При любом изменение менять дату updated_at
NEW.updated_at = CURRENT_TIMESTAMP;
-- Менять дату end_at при неактивном статусе
IF NEW.is_active = FALSE AND OLD.is_active = TRUE THEN
NEW.end_at = CURRENT_TIMESTAMP;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Реализована каскадная деактивация: при деактивации провайдера автоматически деактивируются все связанные с ним тарифы.

Базовые индексы для ускорения чтения:

CREATE INDEX CONCURRENTLY idx_город_провайдер_город_id ON город_провайдер (город_id);
CREATE INDEX CONCURRENTLY idx_ город_провайдер_провайдер_id ON город_провайдер (провайдер_id);
CREATE INDEX CONCURRENTLY idx_тарифы_город_провайдер_id ON тарифы (город_провайдеры_id); -- таблица с тарифами

Что дальше?

В следующей части я планирую рассказать о парсинге на Python vs Selenium.

Буду признателен за критику моей архитектуры БД и кода. Какие дополнения вы бы внесли в структуру SQL (ограничения, улучшение триггера, дополнительные индексы)?

Однажды наткнулся на статью о том, как кто-то реализовал игру DOOM полностью на SQL, стало интересно какие возможности этого языка. Насколько реально перевести весь бэкенд на SQL. Планирую применять Python для парсинга и записи данных в базу. Постепенно планирую заменять некоторые функции Python на SQL. Интересно, насколько можно заменить Python на SQL в реальных задачах пишите ваше мнение.