One of the easiest mistakes in a recharge platform is assuming that every product can be represented as: amount = 10 currency = GBP 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 The purchase price is the same. The product value is not. A schema should therefore avoid assuming that: purchase_amount == recipient_amount 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 For general airtime: { "product_type": "airtime", "purchase_amount": "10.49", "purchase_currency": "EUR", "recipient_value": "10.00", "recipient_currency": "EUR" } For a data bundle: { "product_type": "data_bundle", "purchase_amount": "10.49", "purchase_currency": "EUR", "recipient_value": null, "recipient_currency": null } 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 Better: product_type: - airtime - data_bundle - voice_bundle - sms_bundle - combo_bundle 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 Trying to fit that into the `products` table quickly becomes ugly: data_amount data_unit voice_minutes sms_count social_data_amount ... A normalized allowance model is more flexible. product_allowances ------------------ id product_id allowance_type amount unit Example: product_id | allowance_type | amount | unit ------------------------------------------------ 123 | data | 10 | GB 123 | voice | 200 | minute 123 | sms | 100 | message Now the application can render: 10 GB data 200 minutes 100 SMS 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!" Store it structurally. For example: validity_value = 30 validity_unit = day or: { "validity": { "value": 30, "unit": "day" } } 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 Do not make those your primary application key. Your application should own its identity: products.id = internal UUID products.external_product_id = provider identifier 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 The operator itself should also have an external mapping. operators --------- id country_code name provider_id external_operator_id active 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" } 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. 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 might safely contain: { "provider_label": "Promo Summer 10GB", "campaign_code": "SUMMER26" } 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 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) Then: if exists: update normalized fields else: create product 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 Tomorrow, the provider changes the same SKU to: 4 GB valid for 30 days 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" } } } 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 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.