Arquitectura de Base de Datos para el Parseo de Tarifas en PostgreSQL

# Arquitectura de Base de Datos Relacional para el Monitoreo de Tarifas de Proveedores

Los desarrolladores de proyectos de telecomunicaciones a menudo se enfrentan a la necesidad de almacenar datos dinámicos sobre tarifas de internet y TV. La estructura propuesta de PostgreSQL utiliza un esquema en estrella con la tabla de hechos central tariffs y dimensiones cities, providers. Esto asegura consultas eficientes para análisis y comparación de ofertas entre regiones.

El modelo soporta historial de cambios: las tarifas y proveedores se desactivan sin eliminar registros, lo cual es crítico para auditorías e informes.

Tablas de Dimensiones: Entidades Básicas

Las dimensiones almacenan atributos estáticos. Ciudades es una tabla de búsqueda simple sin versionado.

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

Proveedores incluye marcas de tiempo para rastrear el período de actividad:

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

Tabla de Hechos: Detalles de Tarifas

La tabla central agrega todos los atributos de tarifas con claves foráneas a las dimensiones. Los campos cubren velocidad de internet, paquetes de TV, servicios adicionales y precios.

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

Todas las claves están nombradas para facilitar el mantenimiento:

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

Gestión de Historial con Triggers

Cada entidad mutable (excepto ciudades) tiene start_at, updated_at, end_at. Los triggers automatizan las actualizaciones.

Estructura básica para entidades:

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 ante cambios:

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;

Desactivación en cascada: cuando se establece la end_date de un proveedor, todas las tarifas relacionadas reciben end_at. Esto se implementa mediante triggers en UPDATE y DELETE.

Optimización de Rendimiento

Los índices aceleran los JOIN y filtros por regiones/proveedores. Utiliza creación concurrente para producción:

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

Ventajas de los índices:

• Consultas más rápidas para tarifas actuales (WHERE end_at IS NULL)
• Agregaciones eficientes por monthly_cost, internet_speed
• Soporte para consultas analíticas complejas con GROUP BY por proveedores

Puntos Clave

• Esquema en Estrella: Óptimo para consultas OLAP en análisis de telecomunicaciones, minimiza JOINs.
• Seguimiento de Historial: Triggers automáticos previenen pérdida de datos al desactivar tarifas.
• Restricciones Nombradas: Simplifican migraciones y depuración en proyectos grandes.
• Índices Concurrentes: Permiten agregar sin bloquear tráfico de producción.
• Extensibilidad: Fácil agregar campos para nuevos servicios (VoIP, redes mesh).

— Editorial Team