Database Creation

DDL скрипта за креирање на табелите

ddl telecom.txt

Во базата на податоци се дефинирани табели за целосен телекомуникациски систем кој опфаќа корисници, сметки, договори, претплати, SIM картички, уреди, тарифни планови, дополнителни услуги, користење на услуги, фактурирање, плаќања, CRM поддршка, вработени и мрежна инфраструктура.

Погледи

Во нашата база на податоци има погледи кои би се употребувале за потребите на вработените во телекомуникациската компанија. Тие овозможуваат поедноставно пребарување и анализа на податоци поврзани со корисници, претплати, користење услуги, фактури, плаќања, roaming активност и корисничка поддршка.

Customer Call History

Овој поглед ја прикажува историјата на телефонски повици за одреден корисник. Во него се прикажуваат податоци за корисникот, сметката, претплатата, тарифниот план, договорот, телефонскиот број од кој е направен повикот, бројот кон кој е направен повикот, почетно и крајно време на повикот, времетраење, тип на повик, насока на повик, наплатен износ, fraud score и roaming партнер доколку повикот бил направен во roaming.

Овој поглед би се користел од корисничка поддршка и billing тимот при проверка на детална историја на повици, жалби за наплата или анализа на користење на говорни услуги. Пребарувањето најчесто би се вршело според ID на корисникот, ID на претплатата или телефонски број.

Customer call history.sql

Customer SMS History

Овој поглед ја прикажува SMS историјата на корисникот. Се прикажуваат податоци за корисникот, сметката, претплатата, тарифниот план, договорот, бројот од кој е испратена пораката, бројот кон кој е испратена, времето на испраќање, типот на SMS пораката, насоката, наплатениот износ и roaming партнерот доколку пораката е испратена преку roaming мрежа.

Овој поглед би се користел за проверка на SMS активност, анализа на наплатени пораки и решавање на кориснички жалби поврзани со SMS услуги. Пребарувањето најчесто би се вршело според ID на корисникот или ID на претплатата.

Customer SMS history.sql

Customer Data Usage History

Овој поглед ја прикажува историјата на користење мобилен интернет за корисникот. Се прикажуваат податоци за корисникот, сметката, претплатата, тарифниот план, договорот, почеток и крај на data сесијата, времетраење, потрошени MB и GB, APN, IP адреса, наплатен износ, roaming партнер, земја на roaming партнерот, сектор, фреквентен опсег и генерација на мрежа.

Овој поглед би се користел за анализа на интернет потрошувачка, проверка на roaming data наплата, техничка анализа на сесиите и поддршка при кориснички жалби за мобилен интернет. Пребарувањето најчесто би се вршело според ID на корисникот, ID на претплатата или временски период.

Customer data usage history.sql

Customer Daily Usage Summary

Овој поглед прикажува дневна агрегирана потрошувачка на корисникот. Наместо секој повик, SMS порака или data сесија да се разгледува посебно, овој поглед ги прикажува вкупните дневни вредности: вкупно секунди разговор, вкупно минути разговор, број на SMS пораки, вкупно потрошени MB и GB, како и вкупен наплатен износ за тој ден.

Овој поглед би се користел за брз преглед на дневната активност на корисникот и за billing анализа. Пребарувањето најчесто би се вршело според ID на корисник, ID на претплата или датум.

Customer daily usage summary.sql

Customer Full Usage Activity

Овој поглед дава целосен преглед на користењето услуги од страна на корисникот. Ги обединува податоците од повиците, SMS пораките и data сесиите и прикажува вкупна активност за секоја претплата. Се прикажуваат вкупни секунди и минути разговор, вкупен број SMS пораки, вкупна data потрошувачка во MB и GB, како и вкупен наплатен износ.

Овој поглед би се користел како 360-преглед на корисничка активност. Корисничката поддршка може брзо да провери колку услуги користел корисникот, без да ги отвора деталните CDR табели посебно.

Customer full usage activity.sql

Customer Device SIM History

Овој поглед ја прикажува историјата на доделени SIM картички и уреди на корисникот. Се прикажуваат корисникот, сметката, претплатата, тарифниот план, договорот, уредот што бил доделен, производител, модел, тип на уред, статус на доделување, SIM картичка, MSISDN број, ICCID, IMSI, тип на SIM картичка, статус на SIM картичката и периодот во кој SIM картичката била поврзана со претплатата.

Овој поглед би се користел за следење која SIM картичка и кој уред биле активни кај одреден корисник. Пребарувањето најчесто би се вршело според ID на корисникот, ID на претплатата, MSISDN број или SIM ID.

Customer device SIM history.sql

Customer Invoice Details

Овој поглед ги прикажува деталите за фактурите на корисникот. Се прикажуваат податоци за корисникот, сметката, број на фактура, billing период, датум на издавање, рок на плаќање, вкупен износ, данок, попуст, статус на фактурата, како и ставките од фактурата со опис, количина, единечна цена, line amount и tax rate.

Овој поглед би се користел од billing тимот и корисничката поддршка за објаснување на фактури, проверка на наплатени услуги и анализа на ставки во фактурата. Пребарувањето најчесто би се вршело според ID на корисникот, account number, invoice number или invoice ID.

Customer invoice details.sql

Customer Payment History

Овој поглед ја прикажува историјата на плаќања на корисникот. Се прикажуваат податоци за корисникот, сметката, фактурата, начин на плаќање, давател на payment service, датум на плаќање, платен износ, референтен број и статус на плаќањето.

Овој поглед би се користел за проверка дали корисникот ја платил фактурата, колку има платено, преку кој метод е извршено плаќањето и дали плаќањето е успешно. Пребарувањето најчесто би се вршело според ID на корисникот, account ID, invoice ID или статус на плаќање.

Customer payment history.sql

Customer Addon Invoice Detail

Овој поглед ги прикажува дополнителните услуги што ги има активирано корисникот и нивната поврзаност со фактурирањето. Се прикажуваат податоци за корисникот, сметката, претплатата, addon услугата, типот на addon, цената, allowance вредноста, дали addon услугата е recurring, датум на активација, датум на деактивација, статус, како и поврзана фактура и ставка од фактура.

Овој поглед би се користел за проверка на дополнителни пакети како extra data, roaming addon, SMS пакет или други дополнителни услуги. Пребарувањето најчесто би се вршело според ID на корисникот, ID на претплата или addon ID.

Customer addon invoice detail.sql

Customer Roaming Activity

Овој поглед ја прикажува roaming активноста на корисникот. Во него се обединуваат roaming податоци од повици, SMS пораки и data сесии. Се прикажуваат корисникот, претплатата, типот на активност, roaming партнерот, земјата, времето на настанот, потрошувачката и наплатениот износ.

Овој поглед би се користел за анализа на roaming користење, проверка на трошоци во странство и решавање на кориснички жалби поврзани со roaming наплата. Пребарувањето најчесто би се вршело според ID на корисник, ID на претплата, roaming партнер или земја.

Customer roaming activity.sql

Customer Subscription Status Timeline

Овој поглед ја прикажува временската линија на промени на статусот на претплатата. Се прикажуваат податоци за корисникот, сметката, претплатата, стариот статус, новиот статус, времето на промена, вработениот кој ја направил промената и причината за промена.

Овој поглед би се користел за следење на животниот циклус на претплатата: активирање, суспензија, откажување, истекување или повторна активација. Пребарувањето најчесто би се вршело според ID на корисникот, ID на претплатата или статус.

Customer subscription status timeline.sql

Customer Support Ticket Timeline

Овој поглед ја прикажува историјата на support тикети и интеракции со корисникот. Се прикажуваат тикетите, типот на тикет, subject, опис, priority, статус, доделен вработен, датум на креирање, датум на затворање, интеракции, канал на комуникација, белешки и промени на статус.

Овој поглед би се користел од customer support тимот за следење на проблеми, жалби, технички барања и целосна комуникација со корисникот. Пребарувањето најчесто би се вршело според ID на корисникот, ticket ID, assigned employee ID или статус на тикет.

Customer support ticket timeline.sql

Функции

Во базата на податоци има функции кои овозможуваат пресметка и проверка на важни информации без потреба од рачно пишување сложени SQL прашања.

Customer Subscription Summary

Оваа функција враќа кратко резиме за претплатите на даден корисник. Со неа може брзо да се провери колку претплати има корисникот, колку од нив се активни и кои услуги ги користи.

Функцијата би се користела од customer support и sales тимот за брз преглед на состојбата на корисникот.

Customer subscription summary.sql

Outstanding Customer Balance

Оваа функција го пресметува вкупниот неплатен баланс за даден корисник. Ги зема предвид фактурите и плаќањата и враќа колку корисникот должи.

Функцијата би се користела од billing тимот и customer support при проверка на неплатени фактури, суспензија на услуги или известување на корисникот.

Outstanding customer balance.sql

SIM Ready For Use Check

Оваа функција проверува дали одредена SIM картичка е подготвена за користење. Се проверува дали SIM картичката има соодветен статус и дали веќе не е активна на друга претплата.

Функцијата би се користела пред доделување SIM картичка на претплата, за да се спречи користење на веќе активна или невалидна SIM картичка.

SIM ready for use check.sql

Total Monthly Recurring Charge

Оваа функција ја пресметува вкупната месечна recurring наплата за даден корисник. Во пресметката се земаат активните претплати и активните recurring addons.

Функцијата би се користела за billing пресметки, проценка на месечна сметка и проверка на приход по корисник.

Total monthly recurring charge.sql

Процедури

Во базата на податоци има процедури кои автоматизираат важни операции во телекомуникацискиот систем.

Customer SIM Assignment

Оваа процедура се користи за доделување SIM картичка на претплата и активирање на корисничка услуга. Процедурата го поврзува корисникот, сметката, тарифниот план, претплатата и SIM картичката.

Се користи при активирање нова претплата или при издавање нова SIM картичка на корисник.

Customer sim assignment.sql

Change Plan Procedure

Оваа процедура овозможува промена на тарифниот план на постоечка претплата. Се користи кога корисникот сака да премине на друг пакет, на пример од основен кон поголем пакет со повеќе интернет или минути.

Процедурата би се користела од sales или customer support тимот.

Change plan procedure.sql

Support Ticket Creation

Оваа процедура креира нов support тикет за корисникот. При креирањето се внесуваат корисник, сметка, претплата, доделен вработен, тип на тикет, subject, опис и priority.

Се користи за стандардизирано отворање тикети во CRM системот.

Support ticket creation.sql

Suspend Accounts For Invoice Overdue

Оваа процедура ги обработува задоцнетите фактури. Ако фактурата не е платена по истекување на рокот и grace периодот, процедурата може да го означи account како suspended и да ги суспендира активните претплати поврзани со него.

Се користи како batch процес од billing тимот за автоматска контрола на неплатени фактури.

Suspend accounts for invoice overdue.sql

Тригери

Во базата на податоци има тригери кои автоматски извршуваат бизнис правила при внесување или промена на податоци.

Account Balance Trigger

Овој тригер автоматски го ажурира балансот на сметката кога се внесува или менува плаќање. Ако плаќањето е успешно, current balance на account се намалува. Ако статусот на плаќањето се промени, балансот се ажурира соодветно.

Дополнително, тригерот го ажурира статусот на фактурата според тоа дали е целосно платена, делумно платена или задоцнета.

Account balance trigger.sql

AutoClose Ticket Trigger

Овој тригер автоматски го пополнува полето closed_at кога CRM тикет ќе добие статус resolved или closed. Ако тикетот повторно се отвори, closed_at се брише.

Се користи за автоматска евиденција на времето на затворање на тикети.

AutoClose ticket trigger.sql

One Active Subscription For SIM

Овој тригер спречува една SIM картичка да биде активна на повеќе претплати во исто време. Пред внесување нов запис во историјата на SIM картички, се проверува дали истата SIM картичка веќе има активна доделба без end_date.

Се користи за зачувување на интегритетот на податоците и спречување конфликт при SIM assignment.

One active subscription for SIM.sql

Prevent Already Paid Trigger

Овој тригер спречува плаќање на фактура која веќе е целосно платена. Исто така спречува внесување плаќање кое е поголемо од преостанатиот износ за плаќање.

Се користи за заштита од преплаќање и неконзистентни billing податоци.

Prevent already paid trigger.sql

Subscription Status Change Trigger

Овој тригер автоматски внесува запис во subscription_status_history секогаш кога ќе се промени статусот на претплатата. Се зачувуваат стариот статус, новиот статус, времето на промена, вработениот и причината.

Се користи за целосна историја на статуси на претплатите.

Subscription status change trigger.sql

Кратка анализа на базата

Базата е организирана во неколку логички модули.

Customer and Account Management

Овој дел ги опфаќа табелите customers, customer_addresses, accounts, contracts и billing_cycles. Тие се користат за основна евиденција на корисници, нивни адреси, сметки, договори и billing циклуси.

Subscription and Product Catalog

Овој дел ги опфаќа products, services, plans, plan_services, overage_policies, subscriptions, addons и subscription_addons. Овие табели го дефинираат каталогот на услуги, тарифни планови, претплати и дополнителни услуги.

SIM and Device Management

Овој дел ги опфаќа sim_cards, devices, device_assignments и sim_card_subscription_history. Тие се користат за управување со SIM картички, уреди и нивната поврзаност со претплати.

Usage and CDR Module

Овој дел ги опфаќа usage_cdr_calls, usage_cdr_sms, usage_cdr_data и usage_aggregates_daily. Во нив се чуваат детални и агрегирани податоци за користење на услуги: повици, SMS пораки и мобилен интернет.

Billing and Payments

Овој дел ги опфаќа invoices, invoice_items, payment_methods, payments и billing_runs. Тие се користат за фактурирање, ставки од фактури, начини на плаќање, извршени плаќања и billing batch процеси.

CRM and Customer Support

Овој дел ги опфаќа crm_tickets, crm_interactions, ticket_status_history и employee_assignments. Тие се користат за следење на кориснички проблеми, тикети, интеракции и задолженија на вработени.

Employee Management

Овој дел ги опфаќа employees, departments и employee_roles. Тие се користат за евиденција на вработени, нивни улоги, оддели и хиерархија преку manager_id.

Network Infrastructure

Овој дел ги опфаќа network_sites, cell_towers, tower_sectors, coverage_zones, network_technologies, network_alarms, outages и roaming_partners. Овие табели ја моделираат телекомуникациската мрежна инфраструктура, базни станици, сектори, coverage зони, аларми, прекини и roaming партнери.

Заклучок

Оваа база на податоци претставува целосен телекомуникациски систем кој ги поврзува корисниците, нивните сметки, договори, претплати, SIM картички, уреди, користење на услуги, фактури, плаќања, CRM поддршка и мрежна инфраструктура. Погледите се креирани за да овозможат полесно пребарување и анализа на податоците, додека функциите, процедурите и тригерите овозможуваат автоматизација на важни деловни правила.