Hi everyone,
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?
