Recurring Billing Database Design
atk4, database-design, sql
Solution
I think you may be trying to get too clever with the design and overthinking it. If you think about the business problem, each payment interval is effectively an invoice. Why not just create an invoices table and let a scheduled job insert an invoice at certain intervals based on the periodicity of each account and whether it is active during that interval.
By having an actual invoice row you get an InvoiceID that you can reference when seeking payment from a customer and track the payment status individually for each billing.
Sometimes simple is best.
Problem
I'm writing an application that will involve recurring billing of a monthly (or weekly) fixed amount, and it can last until subscription is canceled. The customer can pay several periods in advance. He can cancel subscription, and then come back after certain unpaid periods. I need the sistem to let me know when a period is past due. So I'm burning my brain on how to design the database (maybe is not a database issue but a programming one), Has any one come to this kind of applications? what approach has been taken?