Datenbankarchitektur für das Parsen von Tarifen auf PostgreSQL

Relationale Datenbankarchitektur für die Überwachung von Anbieter-Tarifen

Entwickler von Telekom-Projekten stehen oft vor der Notwendigkeit, dynamische Daten zu Internet- und TV-Tarifen zu speichern. Die vorgeschlagene PostgreSQL-Struktur verwendet ein Sternschema mit der zentralen Faktentabelle tariffs und den Dimensionen cities, providers. Dies ermöglicht effiziente Abfragen für Analysen und den Vergleich von Angeboten quer durch Regionen.

Das Modell unterstützt die Änderungshistorie: Tarife und Anbieter werden deaktiviert, ohne Datensätze zu löschen, was für Audits und Berichte entscheidend ist.

Dimensions-Tabellen: Basisentitäten

Dimensions speichern statische Attribute. Cities ist eine einfache Nachschlagetabelle ohne Versionierung.

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

Anbieter enthalten Zeitstempel zur Nachverfolgung des Aktivitätszeitraums:

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

Faktentabelle: Tarifdetails

Die zentrale Tabelle aggregiert alle Tarifattribute mit Fremdschlüsseln zu den Dimensions. Felder umfassen Internetgeschwindigkeit, TV-Pakete, Zusatzdienste und Preise.

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

Alle Schlüssel sind benannt, um die Wartung zu erleichtern:

• CONSTRAINT constraint_for_pk_id PRIMARY KEY (id)
• CONSTRAINT constraint_for_fk_id FOREIGN KEY (id) REFERENCES (id)
• CONSTRAINT uq_city_provider UNIQUE (city_id, provider_id)

Historieverwaltung mit Triggers

Jede veränderliche Entität (außer cities) hat start_at, updated_at, end_at. Triggers automatisieren die Updates.

Grundstruktur für Entitäten:

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 bei Änderungen:

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;

Kaskadendeaktivierung: Wenn für einen Anbieter end_date gesetzt wird, erhalten alle zugehörigen Tarife end_at. Dies wird durch Triggers auf UPDATE und DELETE implementiert.

Leistungsoptimierung

Indizes beschleunigen JOINs und Filter nach Regionen/Anbietern. Für die Produktion parallele Erstellung verwenden:

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

Vorteile der Indizierung:

• Schnellere Abfragen für aktuelle Tarife (WHERE end_at IS NULL)
• Effiziente Aggregationen nach monthly_cost, internet_speed
• Unterstützung komplexer analytischer Abfragen mit GROUP BY nach Anbietern

Wichtige Punkte

• Sternschema: Optimal für OLAP-Abfragen in der Telekom-Analytik, minimiert JOINs.
• Historie-Nachverfolgung: Automatische Triggers verhindern Datenverlust bei Deaktivierung von Tarifen.
• Benannte Constraints: Vereinfachen Migrationen und Debugging in großen Projekten.
• Concurrent Indexes: Ermöglichen Hinzufügen ohne Blockade des Produktionsverkehrs.
• Erweiterbarkeit: Einfaches Hinzufügen neuer Felder für Dienste (VoIP, Mesh-Netzwerke).

— Editorial Team