Logic for billing and subscription apps?

Question

We are at the planning stage of a web application offering a subscription to our customers. Subscription periods vary and can be extended indefinitely by our customers, but always at least one month (30 days).

When a customer subscribes, customer information (billing address, phone number, etc.) is stored in the 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 will sort through the subscriptions table (preferably a cronjob) and create invoices for the last subscription period, which are placed in their own table - invoices . Depending on the client, invoices are printed out manually and sent by mail or simply sent to the client by e-mail.

Due to the nature of our customers and the product, we need to offer various payment options, including bank transfer and card payments, so some invoices may need to be manually processed and registered, paid by our employees.

On the 15th every month, the invoices table invoices looped, and if no payment has been marked for the actual invoice, the corresponding subscription will be deleted. If a payment is registered, end_date in the subscriptions table increases for another 30 days (or for which period our client is selected).

Are we looking for headaches, increasing dates back and forth to handle insolvent customers and expand subscriptions? Would it be better to add new subscriptions as customers expand their subscription?

Answer 1

There was a problem in one of the applications that I was working on, and we solved it by tracking which subscription the user had, but did not track the expiration date. Then we kept track of the date on which they were supposed to be billed - so if we wanted to find out which subscription was included, we could just get the last subscription for our account, and if we wanted to see the next time they will be billed, we just check them next_bill_date .

This way you can track user subscriptions and see when they have been updated / downgraded, but your billing code remains simple - and you never have to worry about overlaps (since there are no end dates to deal with in subscriptions).

Answer 2

I do not consider it necessary to create several subscription records for one client, if you have only one type of subscription. If the billing period is always monthly at a fixed price, it will be enough to change the end_date value for the subscription. All you need to know is when his subscription ends, so you can stop billing. Thus, if he expands his subscription, you only need to update one record, so that the account will be renewed next month.

In addition, I think it would be better to flag unpaid subscriptions rather than delete them. If the customer missed the monthly payment, mark the subscription as unpaid, so future invoices (and service) will be stopped. When / if they pay, you release the subscription so that the service life / subsequent months is renewed.

Answer 3

I would use a subscription table to track only subscriptions. This means that when a customer expands their subscription, a new record is inserted.

In addition, I would add a subscription date column to the customer table, which will be updated through the trigger when new subscribers are inserted.

While this is denormalization, this method will allow your web application to check client access to the service without any connection (reducing the load on the db server). Indeed, only a package will need to request a subscription table.

Also, saving your subscription history may be helpful.

• in case of a dispute with a client
• in case of changing the business logic of the enterprise (for example, to give the best discount to customers based on their fidelity).
• for future statistics

When your user base and subscription history becomes huge for mantain, you can decide to periodically create backups by exporting them in a convenient format (I will always use xml, but some enterprise with which I worked with the preferred csv).

Answer 4

To keep track of the subscription history of one client, I would say that it is better to add new subscriptions.

You will also save yourself a headache to understand why the subscription ordered on January 30 expires in March (you know that February is the shortest month ...).