Architecture de base de données pour l'analyse des tarifs sur PostgreSQL

Architecture de base de données relationnelle pour le suivi des tarifs des fournisseurs

Les développeurs de projets télécoms ont souvent besoin de stocker des données dynamiques sur les tarifs internet et TV. La structure PostgreSQL proposée utilise un schéma en étoile avec la table de faits centrale tariffs et les dimensions cities, providers. Cela garantit des requêtes efficaces pour l'analyse et la comparaison des offres entre régions.

Le modèle prend en charge l'historique des changements : les tarifs et fournisseurs sont désactivés sans supprimer les enregistrements, ce qui est crucial pour les audits et rapports.

Tables de dimensions : Entités de base

Les dimensions stockent des attributs statiques. Cities est une table de recherche simple sans versionnage.

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

Providers incluent des horodatages pour suivre la période d'activité :

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

Table de faits : Détails des tarifs

La table centrale agrège tous les attributs de tarif avec des clés étrangères vers les dimensions. Les champs couvrent la vitesse internet, les forfaits TV, les services supplémentaires et la tarification.

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

Toutes les clés sont nommées pour faciliter la maintenance :

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

Gestion de l'historique avec des triggers

Chaque entité mutable (sauf les villes) possède start_at, updated_at, end_at. Les triggers automatisent les mises à jour.

Structure de base pour les entités :

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 sur les changements :

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;

Désactivation en cascade : quand la end_date d'un fournisseur est définie, tous les tarifs liés reçoivent end_at. Cela est implémenté via des triggers sur UPDATE et DELETE.

Optimisation des performances

Les index accélèrent les JOIN et les filtres par régions/fournisseurs. Utiliser la création concurrente pour la production :

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

Avantages de l'indexation :

• Requêtes plus rapides pour les tarifs actuels (WHERE end_at IS NULL)
• Agrégations efficaces par monthly_cost, internet_speed
• Support pour les requêtes analytiques complexes avec GROUP BY par fournisseurs

Points clés

• Schéma en étoile : Optimal pour les requêtes OLAP dans l'analyse télécom, minimise les JOIN.
• Suivi de l'historique : Triggers automatiques préviennent la perte de données lors de la désactivation des tarifs.
• Contraintes nommées : Simplifient les migrations et le débogage dans les grands projets.
• Index concurrents : Permettent l'ajout sans bloquer le trafic de production.
• Extensibilité : Facile d'ajouter des champs pour de nouveaux services (VoIP, mesh networks).

— Editorial Team