Architektura bazy danych do parsowania taryf na PostgreSQL

Architektura relacyjnej bazy danych do monitorowania taryf dostawców

Programiści projektów telekomunikacyjnych często napotykają konieczność przechowywania dynamicznych danych o taryfach internetu i TV. Proponowana struktura w PostgreSQL wykorzystuje schemat „Gwiazda” z centralną tabelą faktów taryfy i wymiarami miasta, dostawcy. Zapewnia to efektywne zapytania dla analityki i porównywania ofert według regionów.

Model wspiera historię zmian: taryfy i dostawcy są deaktywowane bez usuwania rekordów, co jest kluczowe dla audytu i raportów.

Tabele wymiarów: podstawowe encje

Wymiary przechowują statyczne atrybuty. Miasta to prosta tabela referencyjna bez mechanizmu historii.

-- 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';

Dostawcy zawierają znaczniki czasowe do śledzenia okresu aktywności:

-- 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';

Tabela faktów: szczegóły taryf

Centralna tabela agreguje wszystkie atrybuty taryf z kluczami zewnętrznymi do wymiarów. Pola obejmują prędkość internetu, pakiety TV, dodatkowe usługi i ceny.

-- 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)
);

Wszystkie ograniczenia są nazwane dla ułatwienia utrzymania:

• CONSTRAINT pk_id PRIMARY KEY (id)
• CONSTRAINT fk_id FOREIGN KEY (id) REFERENCES tabela (id)
• CONSTRAINT uq_miasto_dostawca UNIQUE (miasto_id, dostawca_id)

Zarządzanie historią za pomocą wyzwalaczy

Każda zmienialna encja (oprócz miast) posiada pola start_at, updated_at, end_at. Wyzwalacze automatyzują aktualizacje.

Podstawowa struktura dla encji:

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»
);

Wyzwalacz na zmiany:

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;

Kaskadowa deaktywacja: przy ustawieniu end_date dostawcy wszystkie powiązane taryfy otrzymują end_at. Jest to realizowane za pomocą wyzwalaczy na UPDATE i DELETE.

Optymalizacja wydajności

Indeksy przyspieszają operacje JOIN i filtry według regionów/dostawców. Tworzenie z opcją CONCURRENTLY dla środowisk produkcyjnych:

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);

Zalety indeksowania:

• Przyspieszenie zapytań o aktualne taryfy (WHERE end_at IS NULL)
• Efektywne agregacje według monthly_cost, internet_speed
• Obsługa złożonych zapytań analitycznych z GROUP BY według dostawców

Co ważne

• Schemat „Gwiazda”: Optymalny dla zapytań OLAP w analityce telekomunikacyjnej, minimalizuje liczbę JOIN-ów.
• Historia: Automatyczne wyzwalacze zapobiegają utracie danych przy deaktywacji taryf.
• Nazwane ograniczenia: Ułatwiają migracje i debugowanie w dużych projektach.
• Indeksy CONCURRENTLY: Pozwalają dodawać bez blokowania ruchu produkcyjnego.
• Rozszerzalność: Łatwo dodać pola dla nowych usług (VoIP, sieci mesh).

— Editorial Team