Telecom OSS/BSS data model
Normalized (3NF) telecom OSS/BSS model. A BSS spine of customer-account-subscription-product feeds billing, while an OSS spine of service-order-service-resource manages provisioning over network elements, SIM cards, and MSISDN inventory, with trouble tickets for assurance. Customer PII and SIM secrets are classified and masked.
Telecommunications operations- and business-support systems modeled on the TM Forum eTOM/SID frameworks: customers and billing accounts, product offerings, subscriptions and provisioned services, fulfillment service orders, network resource inventory, SIM and MSISDN pools, invoicing, and trouble-ticket assurance — as a normalized OLTP schema. It is a normalized (3NF) relational model that mirrors the source system, suited to operational databases and to landing source data faithfully.
- 15
- tables
- 147
- columns
- 17
- relationships
- 7
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: PostgreSQL). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
eTOM/SID-aligned entity model
TM Forum's SID gives the canonical Party/Customer/Product/Service/Resource layering that telecom OSS/BSS systems already speak, so modeling those concepts directly eases integration across stacks.
Separate Customer-Facing and Resource-Facing services
A subscription (CFS) decouples from the provisioned service instance (RFS) and the network elements that realize it, mirroring real telecom fulfillment and keeping the model in 3NF.
Junction tables for resource and number assignment
Services map M:N to network elements, and a service may carry multiple SIM/MSISDN over time; junction tables capture these without denormalizing inventory.
Normalized (3NF) rather than dimensional
OSS/BSS is transactional order-to-activate and bill-run processing where update integrity matters more than analytical roll-ups.
Tables in the Telecom OSS/BSS model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Tables (14)
customer
Grain: one row per customer (party)
SID Party/Customer.
| Column | Type | Keys |
|---|---|---|
| customer_key | bigint | PK |
| customer_number | varchar | BK |
| customer_type | varchar | |
| first_name | varchar | PII |
| last_name | varchar | PII |
| legal_name | varchar | |
| varchar | PII | |
| phone | varchar | PII |
| national_id | varchar | PII |
| segment | varchar | |
| credit_score | integer | |
| status | varchar | |
| acquisition_date | date |
billing_account
Grain: one row per billing account
SID Customer Account.
| Column | Type | Keys |
|---|---|---|
| account_key | bigint | PK |
| customer_key | bigint | |
| account_number | varchar | BK |
| account_name | varchar | |
| billing_cycle_day | smallint | |
| currency | varchar | |
| payment_method | varchar | |
| billing_address | varchar | PII |
| credit_limit | decimal | |
| balance | decimal | |
| status | varchar | |
| opened_date | date |
product_offering
Grain: one row per saleable product/offer
SID Product Offering.
| Column | Type | Keys |
|---|---|---|
| offering_key | bigint | PK |
| offering_code | varchar | BK |
| offering_name | varchar | |
| category | varchar | |
| description | varchar | |
| monthly_recurring_charge | decimal | |
| one_time_charge | decimal | |
| contract_term_months | smallint | |
| data_allowance_gb | decimal | |
| voice_minutes | integer | |
| sms_allowance | integer | |
| is_active | boolean |
subscription
Grain: one row per subscribed product instance
SID Product / subscribed CFS.
| Column | Type | Keys |
|---|---|---|
| subscription_key | bigint | PK |
| account_key | bigint | |
| offering_key | bigint | |
| subscription_number | varchar | BK |
| activation_date | date | |
| contract_end_date | date | |
| renewal_type | varchar | |
| billing_status | varchar | |
| status | varchar | |
| termination_date | date | |
| termination_reason | varchar |
service
Grain: one row per provisioned service instance (RFS)
SID Resource-Facing Service.
| Column | Type | Keys |
|---|---|---|
| service_key | bigint | PK |
| subscription_key | bigint | |
| service_id | varchar | BK |
| service_type | varchar | |
| service_state | varchar | |
| technology | varchar | |
| qos_profile | varchar | |
| provisioned_date | date | |
| deactivated_date | date |
service_order
Grain: one row per service fulfillment order
eTOM Order Handling.
| Column | Type | Keys |
|---|---|---|
| service_order_key | bigint | PK |
| subscription_key | bigint | |
| service_key | bigint | |
| order_number | varchar | BK |
| order_type | varchar | |
| priority | varchar | |
| requested_date | date | |
| completed_date | date | |
| status | varchar | |
| channel | varchar | |
| created_at | timestamp |
network_element
Grain: one row per physical/logical network resource
SID Resource.
| Column | Type | Keys |
|---|---|---|
| network_element_key | bigint | PK |
| element_id | varchar | BK |
| element_type | varchar | |
| vendor | varchar | |
| model | varchar | |
| region | varchar | |
| latitude | decimal | |
| longitude | decimal | |
| capacity | varchar | |
| status | varchar | |
| commissioned_date | date |
service_resource
Grain: one row per service-to-network-element assignment
Junction: which network elements serve a service.
| Column | Type | Keys |
|---|---|---|
| service_resource_key | bigint | PK |
| service_key | bigint | |
| network_element_key | bigint | |
| role | varchar | |
| port_identifier | varchar | |
| assigned_date | date | |
| released_date | date |
sim_card
Grain: one row per SIM/eSIM
Mobile SIM inventory.
| Column | Type | Keys |
|---|---|---|
| sim_card_key | bigint | PK |
| iccid | varchar | BK |
| imsi | varchar | PII |
| ki | varchar | |
| sim_type | varchar | |
| form_factor | varchar | |
| status | varchar | |
| activation_date | date |
msisdn
Grain: one row per phone number in inventory
Number inventory pool.
| Column | Type | Keys |
|---|---|---|
| msisdn_key | bigint | PK |
| phone_number | varchar | BK |
| number_range | varchar | |
| number_type | varchar | |
| status | varchar | |
| reserved_until | date |
service_assignment
Grain: one row per service to SIM/MSISDN assignment
Junction: SIM/MSISDN allocated to a mobile service.
| Column | Type | Keys |
|---|---|---|
| service_assignment_key | bigint | PK |
| service_key | bigint | |
| sim_card_key | bigint | |
| msisdn_key | bigint | |
| assigned_date | date | |
| released_date | date | |
| is_primary | boolean |
invoice
Grain: one row per issued invoice
BSS billing document.
| Column | Type | Keys |
|---|---|---|
| invoice_key | bigint | PK |
| account_key | bigint | |
| invoice_number | varchar | BK |
| invoice_date | date | |
| due_date | date | |
| billing_period_start | date | |
| billing_period_end | date | |
| subtotal_amount | decimal | |
| tax_amount | decimal | |
| total_amount | decimal | |
| amount_paid | decimal | |
| status | varchar |
invoice_line
Grain: one row per invoice line item
Invoice detail.
| Column | Type | Keys |
|---|---|---|
| invoice_line_key | bigint | PK |
| invoice_key | bigint | |
| subscription_key | bigint | |
| line_number | integer | |
| charge_type | varchar | |
| description | varchar | |
| quantity | decimal | |
| unit_price | decimal | |
| amount | decimal | |
| tax_amount | decimal |
trouble_ticket
Grain: one row per fault/trouble ticket
eTOM Problem Handling.
| Column | Type | Keys |
|---|---|---|
| trouble_ticket_key | bigint | PK |
| customer_key | bigint | |
| service_key | bigint | |
| network_element_key | bigint | |
| ticket_number | varchar | BK |
| category | varchar | |
| priority | varchar | |
| severity | varchar | |
| summary | varchar | |
| status | varchar | |
| opened_at | timestamp | |
| resolved_at | timestamp | |
| sla_breached | boolean | |
| resolution_notes | varchar |
Lookup tables (1)
service_type_ref
Grain: static reference of service types
Reference list.
| Column | Type | Keys |
|---|---|---|
| service_type_key | bigint | PK |
| type_code | varchar | BK |
| type_name | varchar | |
| network_domain | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- billing_account.customer_key → customer (many-to-one)
- subscription.account_key → billing_account (many-to-one)
- subscription.offering_key → product_offering (many-to-one)
- service.subscription_key → subscription (many-to-one)
- service_order.subscription_key → subscription (many-to-one)
- service_order.service_key → service (many-to-one)
- service_resource.service_key → service (many-to-one)
- service_resource.network_element_key → network_element (many-to-one)
- service_assignment.service_key → service (many-to-one)
- service_assignment.sim_card_key → sim_card (many-to-one)
- service_assignment.msisdn_key → msisdn (many-to-one)
- invoice.account_key → billing_account (many-to-one)
- invoice_line.invoice_key → invoice (many-to-one)
- invoice_line.subscription_key → subscription (many-to-one)
- trouble_ticket.customer_key → customer (many-to-one)
- trouble_ticket.service_key → service (many-to-one)
- trouble_ticket.network_element_key → network_element (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.