PostgreSQL에서 요금제 파싱을 위한 데이터베이스 아키텍처

# 공급자 요금 모니터링을 위한 관계형 데이터베이스 아키텍처

통신 프로젝트 개발자들은 인터넷 및 TV 요금제의 동적 데이터를 저장해야 하는 상황을 자주 맞닥뜨립니다. 제안된 PostgreSQL 구조는 중앙 팩트 테이블 tariffs와 차원 테이블 cities, providers를 사용하는 Star schema를 채택하여 지역 간 제안 비교를 위한 분석 쿼리를 효율적으로 처리합니다.

이 모델은 변경 이력을 지원합니다: 요금제와 공급자는 기록을 삭제하지 않고 비활성화되며, 이는 감사 및 보고서 작성에 필수적입니다.

차원 테이블: 기본 엔티티

차원 테이블은 정적 속성을 저장합니다. Cities 테이블은 버전 관리가 없는 간단한 조회 테이블입니다.

-- 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 테이블은 활동 기간을 추적하기 위해 타임스탬프를 포함합니다:

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

팩트 테이블: 요금제 상세 정보

중앙 테이블은 모든 요금제 속성을 집계하며 차원 테이블로의 외래 키를 포함합니다. 필드는 인터넷 속도, TV 패키지, 추가 서비스, 가격 정보를 다룹니다.

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

Cascade 비활성화: 공급자의 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