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

Modeling airtime, data, and bundles efficiently is a challenge for recharge platforms. A common mistake is treating every product as a simple amount in a specific currency. However, catalogs often contain diverse products like general airtime, data bundles with varying validity, local minutes, and SMS.

To accurately model these products, separate commercial value from included allowance. For example, two products with the same price may differ in what the recipient receives. Instead of assuming purchase_amount equals recipient_amount, a schema should account for differences. Start with a base model containing product_id, provider_id, external_product_id, operator_id, country_code, product_type, display_name, purchase_amount, purchase_currency, and recipient_value.

When dealing with data bundles, use an explicit product_type instead of inferring it from the name. Options could include airtime, data_bundle, voice_bundle, sms_bundle, and combo_bundle. This improves filtering, validation, analytics, UI rendering, and reporting. Keep presentation data (product name) separate from application data (product type).

For bundles containing multiple allowances, model them separately. For instance, a bundle might include 10 GB data, 200 voice minutes, and 100 SMS. Rather than adding new columns for each future bundle type, use a normalized allowance model with id, product_id, allowance_type, amount, and unit. This allows the application to render bundle details without adding new columns for every future bundle type.

Validity is a fundamental product characteristic that should be stored structurally rather than buried in description text. For example, a validity_value of 30 and validity_unit of day allows for sorting bundles by duration, warning users about short validity, and consistent formatting. If a product does not provide a meaningful validity period, make that state explicit.

External providers often have their own IDs (e.g., ABC-UK-10, prod_847261). Do not use these as your primary application key. Instead, own your own identity with an internal UUID and keep the external provider ID as an identifier. This ensures stability even if the integration changes. The operator is crucial in determining product validity, so store the operator relationship with a countries → operators → products structure.

Keep internal operator identity separate from provider-specific representation to accommodate multiple provider mappings later on.

Avoid using JSON for everything, as it becomes cumbersome when querying structured data. Instead, use structured columns/tables for business-critical attributes and JSON for sparse provider-specific metadata. For example, products.metadata could contain provider_label and campaign_code. Keep raw provider payload separate from structured data to maintain flexibility when ingesting provider catalogs.