Architektura DB pro parsování tarifů na PostgreSQL

# Architektura relační databáze pro monitorování tarifů poskytovatelů

Vývojáři telekomunikačních projektů se často setkávají s potřebou ukládat dynamická data o tarifech internetu a TV. Navržená struktura na PostgreSQL využívá schéma „Hvězda“ s centrální faktovou tabulkou plans a dimenzemi cities, providers. To zajišťuje efektivní dotazy pro analýzu a srovnání nabídek podle regionů.

Model podporuje historizaci změn: tarify a poskytovatelé se deaktivují bez odstraňování záznamů, což je klíčové pro audity a reporty.

Tabulky dimenzí: základní entity

Dimenzím se ukládají statické atributy. Města – jednoduchá referenční tabulka bez historizace.

-- Goroda
CREATE TABLE IF NOT EXISTS cities (
	id BIGINT GENERATED ALWAYS AS IDENTITY,
	city_name VARCHAR (100) NOT NULL,
	CONSTRAINT pk_cities_id PRIMARY KEY (id)
);

COMMENT ON TABLE cities IS 'Table with gorodami Rossii';
COMMENT ON COLUMN cities.id IS 'Unique identifier cities';
COMMENT ON COLUMN cities.city_name IS 'Name cities';

Poskytovatelé zahrnují časové razítka pro sledování období aktivity:

-- Provaydery
CREATE TABLE IF NOT EXISTS providers (
	id BIGINT GENERATED ALWAYS AS IDENTITY,
	provider_name VARCHAR (100),
	start_date DATE DEFAULT CURRENT_DATE,
	end_date DATE DEFAULT NULL,
	CONSTRAINT pk_providers_id PRIMARY KEY (id)
);

COMMENT ON TABLE providers IS 'Table with imenami provayderov';
COMMENT ON COLUMN providers.id IS 'Unique identifier provider';
COMMENT ON COLUMN providers.provider_name IS 'Name provider';
COMMENT ON COLUMN providers.start_date IS 'Date nachala action provider';
COMMENT ON COLUMN providers.end_date IS 'Date okonchaniya action provider';

Faktová tabulka: detailizace tarifů

Centrální tabulka agreguje všechny atributy tarifů s cizími klíči na dimenze. Pole pokrývají rychlost internetu, TV balíčky, doplňkové služby a cenotvorbu.

-- Tarify
CREATE TABLE IF NOT EXISTS plans(
	id BIGINT GENERATED ALWAYS AS IDENTITY,
	city_id BIGINT,
    provider_id BIGINT,
	tariff_name VARCHAR(100) NOT NULL, -- name tarifa
	internet_speed INT, -- skorost interneta
	tv_channels INT, -- count kanalov
	has_cinema VARCHAR(255), -- onlayn kinoteatr
	has_mobile VARCHAR(255), -- mobilnaya connection
	other VARCHAR(255), -- prochee
	connection_cost DECIMAL(10, 2) DEFAULT 0, -- price for podklyuchenie
	monthly_cost DECIMAL(10, 2) NOT NULL, -- ezhemesyachnaya oplata
	discount_cost DECIMAL(10, 2), -- price so skidkoy
	discount_description VARCHAR(255), -- skidochnye sentences, description
	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)
);

Všechny klíče jsou pojmenované pro usnadnění údržby:

• CONSTRAINT ogranichenie_for_pk_id PRIMARY KEY (id)
• CONSTRAINT ogranichenie_for_fk_id FOREIGN KEY (id) REFERENCES (id)
• CONSTRAINT uq_city_provider UNIQUE (city_id, provider_id)

Řízení historizace prostřednictvím triggerů

Každá měnitelná entita (kromě měst) má start_at, updated_at, end_at. Trigery automatizují aktualizace.

Základní struktura pro entity:

CREATE TABLE IF NOT EXISTS suschnosti (
 -- ...
 start_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 end_at TIMESTAMP DEFAULT NULL, -- NULL znachit «deystvuet to sikh por»
);

Trigger na změny:

CREATE OR REPLACE FUNCTION function_reagiruyuschaya_on_changes()
RETURNS TRIGGER AS $$
BEGIN
-- When any change menyat datu updated_at
NEW.updated_at = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Kaskádová deaktivace: při end_date poskytovatele všechny související tarify obdrží end_at. To je realizováno prostřednictvím triggerů na UPDATE a DELETE.

Optimalizace výkonu

Indexy zrychlují JOINy a filtry podle regionů/poskytovatelů. Vytváření concurrent pro produkci:

CREATE INDEX CONCURRENTLY idx_city_provider_city_id ON city_provider (city_id);
CREATE INDEX CONCURRENTLY idx_city_provider_provider_id ON city_provider (provider_id);
CREATE INDEX CONCURRENTLY idx_plans_city_provider_id ON plans (city_providers_id);

Výhody indexování:

• Zrychlení dotazů na aktuální tarify (WHERE end_at IS NULL)
• Efektivní agregace podle monthly_cost, internet_speed
• Podpora složitých analytických dotazů s GROUP BY podle poskytovatelů

Co je důležité

• Schéma „Hvězda“: Optimální pro OLAP dotazy v telekomunikační analytice, minimalizuje JOINy.
• Historizace: Automatické trigery zabraňují ztrátě dat při deaktivaci tarifů.
• Pojmenované omezení: Zjednodušují migrace a ladění ve velkých projektech.
• Indexy concurrent: Umožňují přidávání bez blokování produkčního provozu.
• Rozšiřitelnost: Snadno přidat pole pro nové služby (VoIP, mesh-sítě).

— Editorial Team