Ask HN: How to design database schema for usage based billing?
I am no sql expert, but have some recent experience designing similar systems and went through the db structures of several big oss projects for inspiration (some lost HN post, if anybody remembers).<p>I think your account_debit and account_credit tables have their names swapped.<p>You should have a products table, and a billing tier table with timestamps for start and end, so that accounts billing tier * products billing base rate at the time of use = their cost. You are not going to do only one thing or have only one price forever.<p>So at least:<p>accounts (acct_id, timestamp_start, timestamp_end, tier_id) - this gets more than one row per acct based on whether they change rate tiers. Same thing true for products and tiers below.<p>products (prod_id, timestamp_start, timestamp_end, base_rate)<p>tiers (tier_id, rate_multiplier, product_id, timestamp_start, timestamp_end) - include product_id if you want use different multipliers for the same account on different products in the future. timestamp_start and _end so you can use the right multiplier when prices change for active accounts.<p>you can include an `active` boolean on any of those table to speed up queries if necessary.<p>Then, in account_debit (labelled account_credit above), I would just log usage (debit_id, timestamp_start, timestamp_end, product_id, account_id), and calculate out only when actually billed. The way you have it now you could never deal with a usage dispute. You need better logging of raw data, never store calculation results when you can trivially store numbers.<p>account_credit looks fine, maybe store source_transaction_id for ease of lookup. I don&#x27;t have the experience in footguns as indicated with foreign key constraints by others, that seems worth listening to ;). I think using timestamp_start and _end so that there are never any changes to tier rate rows, product base price rows, etc, should go a long way towards mitigating that here.