DEV Community

Cover image for MobileTopUP: Modeling Airtime, Data, and Bundles Without a Messy Product Schema
MobileTopUP profile image MobileTopUP
MobileTopUP for MobileTopUP

Posted on

MobileTopUP: Modeling Airtime, Data, and Bundles Without a Messy Product Schema

#webdev #backend #architecture #database

One of the easiest mistakes in a recharge platform is assuming that every product can be represented as:

amount = 10
currency = GBP
Enter fullscreen mode Exit fullscreen mode

That works beautifully until the catalogue contains:

  • £10 general airtime;
  • 5 GB valid for 7 days;
  • 20 GB valid for 30 days;
  • 100 local minutes;
  • 500 SMS;
  • 10 GB + 100 minutes;
  • unlimited social data for 3 days;
  • operator-specific promotional bundles.

At that point, amount is no longer a product model.

It is one attribute among many.

This article looks at a practical way to model operator-provided prepaid products without creating either an enormous table full of nullable columns or an unstructured JSON blob that becomes impossible to query.

Start by separating commercial value from included allowance

Consider these two products:

Product A
Price: 10 EUR
Recipient receives: 10 EUR airtime

Product B
Price: 10 EUR
Recipient receives: 8 GB data for 14 days
Enter fullscreen mode Exit fullscreen mode

The purchase price is the same.

The product value is not.

A schema should therefore avoid assuming that:

purchase_amount == recipient_amount
Enter fullscreen mode Exit fullscreen mode

A useful base model might begin with:

products
--------
id
provider_id
external_product_id
operator_id
country_code
product_type
display_name
purchase_amount
purchase_currency
recipient_value
recipient_currency
active
Enter fullscreen mode Exit fullscreen mode

For general airtime:

{
  "product_type": "airtime",
  "purchase_amount": "10.49",
  "purchase_currency": "EUR",
  "recipient_value": "10.00",
  "recipient_currency": "EUR"
}
Enter fullscreen mode Exit fullscreen mode

For a data bundle:

{
  "product_type": "data_bundle",
  "purchase_amount": "10.49",
  "purchase_currency": "EUR",
  "recipient_value": null,
  "recipient_currency": null
}
Enter fullscreen mode Exit fullscreen mode

The bundle needs a different representation.

Use an explicit product type

Do not infer product behaviour from its name.

Bad:

if product.name contains "GB":
    product is data
Enter fullscreen mode Exit fullscreen mode

Better:

product_type:
- airtime
- data_bundle
- voice_bundle
- sms_bundle
- combo_bundle
Enter fullscreen mode Exit fullscreen mode

Your exact taxonomy may differ, but it should be explicit.

This improves:

  • filtering;
  • validation;
  • analytics;
  • UI rendering;
  • reporting;
  • tests.

The product name remains presentation data.

The type becomes application data.

Model allowances separately

A combined bundle may contain more than one allowance. A practical example of why this distinction matters can be seen in mobile data bundles, where data allowance, validity and operator-specific conditions need to be represented separately from general airtime.

For example:

10 GB data
200 voice minutes
100 SMS
valid for 30 days
Enter fullscreen mode Exit fullscreen mode

Trying to fit that into the products table quickly becomes ugly:

data_amount
data_unit
voice_minutes
sms_count
social_data_amount
...
Enter fullscreen mode Exit fullscreen mode

A normalized allowance model is more flexible.

product_allowances
------------------
id
product_id
allowance_type
amount
unit
Enter fullscreen mode Exit fullscreen mode

Example:

product_id | allowance_type | amount | unit
------------------------------------------------
123        | data           | 10     | GB
123        | voice          | 200    | minute
123        | sms            | 100    | message
Enter fullscreen mode Exit fullscreen mode

Now the application can render:

10 GB data
200 minutes
100 SMS
Enter fullscreen mode Exit fullscreen mode

without adding a new database column for every future bundle type.

Validity deserves its own fields

Validity is core product behaviour.

It should not be buried only inside description text such as:

"Awesome 10GB package valid for 30 days!"
Enter fullscreen mode Exit fullscreen mode

Store it structurally.

For example:

validity_value = 30
validity_unit = day
Enter fullscreen mode Exit fullscreen mode

or:

{
  "validity": {
    "value": 30,
    "unit": "day"
  }
}
Enter fullscreen mode Exit fullscreen mode

This enables:

  • sorting bundles by duration;
  • warning users about short validity;
  • consistent formatting;
  • analytics;
  • comparison logic.

Some products may not provide a meaningful validity period.

Make that state explicit rather than pretending every product has one.

Keep provider identity separate from internal identity

External providers often have their own IDs:

ABC-UK-10
prod_847261
sku_2291
Enter fullscreen mode Exit fullscreen mode

Do not make those your primary application key.

Your application should own its identity:

products.id = internal UUID
products.external_product_id = provider identifier
Enter fullscreen mode Exit fullscreen mode

Why?

Because providers can:

  • rename SKUs;
  • migrate APIs;
  • reuse external conventions;
  • return duplicates across environments;
  • be replaced.

Internal IDs should remain stable even if the integration changes.

The operator is part of the product context

A data package is usually not globally valid simply because it says “5 GB.”

It belongs to an operator and market context.

A basic relationship might look like:

countries
    ↓
operators
    ↓
products
Enter fullscreen mode Exit fullscreen mode

The operator itself should also have an external mapping.

operators
---------
id
country_code
name
provider_id
external_operator_id
active
Enter fullscreen mode Exit fullscreen mode

This lets you distinguish internal operator identity from provider-specific representation.

If two providers support the same operator, you can later model multiple provider mappings without duplicating your entire conceptual catalogue.

Avoid using JSON for everything

JSON columns are tempting.

You can store:

{
  "anything": "the provider returns"
}
Enter fullscreen mode Exit fullscreen mode

and ship the feature quickly.

The problem appears later when you need queries such as:

Find all active data bundles
with at least 5 GB
valid for at least 14 days
for operator X.
Enter fullscreen mode Exit fullscreen mode

Structured fields are much easier to work with.

A good compromise is:

  • structured columns/tables for business-critical attributes;
  • JSON for sparse provider-specific metadata.

For example:

products.metadata
Enter fullscreen mode Exit fullscreen mode

might safely contain:

{
  "provider_label": "Promo Summer 10GB",
  "campaign_code": "SUMMER26"
}
Enter fullscreen mode Exit fullscreen mode

while product type, allowance, currency and validity stay queryable.

Preserve the raw provider payload

Structured data and raw data solve different problems.

When ingesting a provider catalogue, it can be useful to retain the original payload separately:

provider_product_snapshots
--------------------------
id
provider_id
external_product_id
payload_json
received_at
Enter fullscreen mode Exit fullscreen mode

This is valuable when debugging:

Why did this product suddenly become inactive?

or:

Did the provider actually send a different validity period yesterday?

The raw snapshot is evidence.

Your normalized tables are application state.

Do not confuse the two.

Catalogue ingestion should be idempotent too

Provider catalogues are often refreshed repeatedly.

An importer should not create a new product every time it sees the same external SKU.

A typical upsert key might be:

(provider_id, external_product_id)
Enter fullscreen mode Exit fullscreen mode

Then:

if exists:
    update normalized fields
else:
    create product
Enter fullscreen mode Exit fullscreen mode

You also need a strategy for products that disappear.

Options include:

  • mark inactive after a successful full catalogue sync;
  • use provider-specific deletion signals;
  • expire products not seen for a defined period.

Do not simply delete records immediately.

Historical transactions may still reference them.

Transactions should snapshot the purchased product

This is critical.

Imagine a customer buys:

5 GB
valid for 30 days
Enter fullscreen mode Exit fullscreen mode

Tomorrow, the provider changes the same SKU to:

4 GB
valid for 30 days
Enter fullscreen mode Exit fullscreen mode

If your transaction history dynamically joins the current product table, yesterday's receipt may appear to change.

Instead, snapshot the relevant product attributes at purchase time.

For example:

{
  "transaction_product": {
    "name": "5 GB Data",
    "type": "data_bundle",
    "allowances": [
      {
        "type": "data",
        "amount": 5,
        "unit": "GB"
      }
    ],
    "validity": {
      "value": 30,
      "unit": "day"
    }
  }
}
Enter fullscreen mode Exit fullscreen mode

Historical transactions should describe what was purchased then.

Not what the catalogue says now.

A practical model

The result can stay relatively small:

providers
countries
operators
products
product_allowances
provider_product_snapshots
transactions
transaction_product_snapshots
Enter fullscreen mode Exit fullscreen mode

You do not need fifty tables.

You also do not need to store the entire business model inside one products.json.

The core idea is to separate:

identity

from

commercial price

from

recipient value

from

allowances

from

validity

from

provider-specific metadata.

Once those concepts are separate, airtime and complex bundles can live in the same catalogue without pretending they are the same product.

AI disclosure: This article was prepared with AI assistance. The publishing editor should review the schema examples and factual accuracy before publication.

Top comments (0)