PostgreSQL 上解析资费的数据库架构

# 运营商资费监控的关系型数据库架构

电信项目开发者常常需要存储互联网和电视资费的动态数据。所提出的 PostgreSQL 结构采用 Star schema，中心事实表为 tariffs，维度表为 cities、providers。这确保了分析查询和跨区域比较套餐的高效执行。

该模型支持变更历史：资费和运营商通过停用而非删除记录来处理，这对于审计和报表至关重要。

维度表：基本实体

维度表存储静态属性。城市表是一个简单的查找表，不支持版本控制。

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

运营商表包含时间戳，用于跟踪活跃期：

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

事实表：资费详情

中心表通过外键引用维度表，聚合所有资费属性。字段涵盖互联网速度、电视套餐、附加服务和定价。

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

所有键都已命名，便于维护：

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

使用触发器管理历史记录

每个可变实体（城市除外）都有 start_at、updated_at、end_at。触发器自动处理更新。

实体基本结构：

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

变更触发器：

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;

级联停用：当设置运营商的 end_date 时，所有相关资费将获得 end_at。这通过 UPDATE 和 DELETE 上的触发器实现。

性能优化

索引加速按区域/运营商的 JOIN 和过滤。在生产环境中使用并发创建：

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

索引的优势：

• 当前资费查询更快（WHERE end_at IS NULL）
• 按 monthly_cost、internet_speed 的高效聚合
• 支持按运营商的 GROUP BY 复杂分析查询

关键点

• Star Schema：电信分析 OLAP 查询的最佳选择，减少 JOIN 操作。
• 历史跟踪：自动触发器防止停用资费时数据丢失。
• 命名约束：简化大型项目的迁移和调试。
• 并发索引：允许在不阻塞生产流量的情况下添加。
• 可扩展性：易于为新服务（VoIP、mesh 网络）添加字段。

— Editorial Team