Application logic for invoicing and subscriptions?

Question

We're just in the planning stage of a web app that offers subscriptions to our customers. The subscription periods varies and can be prolonged indefinitely by our customers, but are always at least one month (30 days).

When a customer signs up, the customer information (billing address, phone number and so on) are stored in a customers table and a subscription is created in the subscriptions table:

id    |    start_date    |     end_date    | customer_id
--------------------------------------------------------
1     |    2010-12-31    |     2011-01-31  | 1

Every month we'll loop through the subscriptions table (cronjob preferably) and create invoices for the past subscription period, which are housed in their own table - invoices. Depending on the customer, invoices are manually printed out and sent by mail, or just emailed to the customer.

Due to the nature of our customers and the product, we need to offer a variety of different payment alternatives including wire transfer and card payments, hence some invoices may need to be manually handled and registered as paid by our staff.

The 15th every month, the invoices table are looped through and if no payment has been marked for the actual invoice, the according subscription will be removed. If there's a payment registered, the end_date in the subscriptions table is incremented by another 30 days (or what now our period our customer has chosen).

Are we looking at headaches by incrementing dates forwards and backwards to handle non-paying customers and extending subscriptions? Would it be a better idea to add new subscriptions as customers extends their subscription?

Answer 1

One of the applications that I worked on had this problem, and we solved it by keeping track of what subscription a user had, but not tracking the expiration date. Then, we kept track of the date they were supposed to be billed on on their account - so if we wanted to see what subscription someone was on, we could just retrieve the latest subscription record for their account, and if we wanted to see the next time they'd be billed, we'd just check their next_bill_date.

By doing it this way, you can track a user's subscriptions and see when they've upgraded/downgraded, but your billing code stays simple - and you never have to worry about overlaps (because subscriptions don't have end dates to deal with).

Answer 2

I don't think its necessary to create multiple subscription records for a single customer so long as you only have a single subscription type. If the billing period is always monthly at a fixed price, altering the subscription end_date will suffice. All you need to know is when his subscription runs out so you can stop invoicing. So, if he extends his subscription, you only need to update a single record so billing will resume next month.

Also, I think it would a better idea to flag unpaid subscriptions instead of deleting them. If a customer were to miss a monthly payment, flag the subscription as unpaid so future invoices (and service) is halted. When/if they do pay, you unflag the subscription so service/next months invoice is resumed.