Recurring Billing Database Design

Question

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?

Answer 1

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.

Answer 2

I think you are over complicating it.

Create a table user:

pk id_user
nm_user
fl_status (active, canceled, pendent, etc)

Create a table subscription one user to many subscriptions:

pk id_subscription
fk id_user
fl_type (maybe there are different subscriptions, with different prices)
dt_in
dt_out

Create a table payment one subscription to many payments:

pk id_payment
fk id_subscription
fl_type (card, payment order, something else)
fl_status (generated, sent, confirmed, canceled because of subscription canceled, canceled because of something else, etc)
dt_generated
dt_sent
dt_confirmed
dt_canceled
[I think you will need another fields to follow and confirm the payment, but it depends of your payment model)

Now you will need to build a few robots that will run everyday at some specific time.

If you get all the active clients and the last payment of each one you will know if a new payment needs to be generated if the last confirmed payment was made more than x days compared to the actual date (it depends if it is prepaid, postpaid, etc). If yes, generated a new payment order.

A robot will send a email or something with the order (and flag then as).

Another robot will confirm the payment using your payment model.

Off course, you need to define your model very well because each user status needs a robot to keep things going until it gets to be canceled or sent to a judge because lack of payment. it's a lot of job to do, but there's no big deal.

ps: if it comes to be a more complex system the database will persist, you will have all the information you need, you have a log of every order, you know what happened to each of then because they have dates and status. You can estimate, montly, how many due dates you will have, how many will pay after a day, after two, and so on.