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

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

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

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

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

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

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

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

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

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

Таблицы измерений (Dimension Tables)

-- Города
CREATE TABLE IF NOT EXISTS города (
	id BIGINT GENERATED ALWAYS AS IDENTITY,
	city_name VARCHAR (100) NOT NULL,
	CONSTRAINT pk_города_id PRIMARY KEY (id)
);

COMMENT ON TABLE города IS 'Таблица с городами России';
COMMENT ON COLUMN города.id IS 'Уникальный идентификатор города';
COMMENT ON COLUMN города.city_name IS 'Название города';


-- Провайдеры
CREATE TABLE IF NOT EXISTS провайдеры (
	id BIGINT GENERATED ALWAYS AS IDENTITY,
	provider_name VARCHAR (100),
	start_date DATE DEFAULT CURRENT_DATE,
	end_date DATE DEFAULT NULL,
	CONSTRAINT pk_провайдеры_id PRIMARY KEY (id)
);

COMMENT ON TABLE провайдеры IS 'Таблица с именами провайдеров';
COMMENT ON COLUMN провайдеры.id IS 'Уникальный идентификатор провайдера';
COMMENT ON COLUMN провайдеры.provider_name IS 'Название провайдера';
COMMENT ON COLUMN провайдеры.start_date IS 'Дата начала действия провайдера';
COMMENT ON COLUMN провайдеры.end_date IS 'Дата окончания действия провайдера';

Таблица фактов (Fact Table)

-- Тарифы
CREATE TABLE IF NOT EXISTS тарифы(
	id BIGINT GENERATED ALWAYS AS IDENTITY,
	город_id BIGINT,
    провайдер_id BIGINT,
	tariff_name VARCHAR(100) NOT NULL, -- название тарифа
	internet_speed INT, -- скорость интернета
	tv_channels INT, -- количество каналов
	has_cinema VARCHAR(255), -- онлайн кинотеатр
	has_mobile VARCHAR(255), -- мобильная связь
	other VARCHAR(255), -- прочее
	connection_cost DECIMAL(10, 2) DEFAULT 0, -- цена за подключение
	monthly_cost DECIMAL(10, 2) NOT NULL, -- ежемесячная оплата
	discount_cost DECIMAL(10, 2), -- цена со скидкой
	discount_description VARCHAR(255), -- скидочные предложения, описание
	start_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	end_at TIMESTAMP DEFAULT NULL,
	CONSTRAINT pk_tariffs_id PRIMARY KEY (id),
	CONSTRAINT fk_tariffs_city_providers_id FOREIGN KEY (city_providers_id) REFERENCES city_providers (id)
);

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

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;
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 в реальных задачах пишите ваше мнение.