Database Architecture for Parsing Tariffs on PostgreSQL

Relational Database Architecture for Provider Tariff Monitoring

Telecom project developers often face the need to store dynamic data on internet and TV tariffs. The proposed PostgreSQL structure uses a Star schema with the central fact table tariffs and dimensions cities, providers. This ensures efficient queries for analytics and comparing offers across regions.

The model supports change history: tariffs and providers are deactivated without deleting records, which is critical for audits and reports.

Dimension Tables: Basic Entities

Dimensions store static attributes. Cities is a simple lookup table without versioning.

-- 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 include timestamps to track the activity period:

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

Fact Table: Tariff Details

The central table aggregates all tariff attributes with foreign keys to dimensions. Fields cover internet speed, TV packages, additional services, and pricing.

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

All keys are named for ease of 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)

Managing History with Triggers

Each mutable entity (except cities) has start_at, updated_at, end_at. Triggers automate updates.

Basic structure for entities:

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 on changes:

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;

Cascade deactivation: when a provider's end_date is set, all related tariffs receive end_at. This is implemented through triggers on UPDATE and DELETE.

Performance Optimization

Indexes speed up JOINs and filters by regions/providers. Use concurrent creation for 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);

Advantages of indexing:

• Faster queries for current tariffs (WHERE end_at IS NULL)
• Efficient aggregations by monthly_cost, internet_speed
• Support for complex analytical queries with GROUP BY by providers

Key Points

• Star Schema: Optimal for OLAP queries in telecom analytics, minimizes JOINs.
• History Tracking: Automatic triggers prevent data loss when deactivating tariffs.
• Named Constraints: Simplify migrations and debugging in large projects.
• Concurrent Indexes: Allow adding without blocking production traffic.
• Extensibility: Easy to add fields for new services (VoIP, mesh networks).

— Editorial Team