How can I improve database design for my subscription based billing system?

Question

I am moving from Credits-based billing system to Subscription-based billing system. Till now, I used to check the number of credits a user had and based on that; I used to E-mail user whether the renewal has been done or not. In the event of a first purchase, I used to activate a plan that user has selected.

I am now shifting to the subscription-based billing system in which I will store subscriptions with validity and I want to eliminate the credits part from the root. Here is the database schema for my new billing system. The issue with this schema is I can't process first purchase event as there is no solid way to know if a user is purchasing for the first time and if a user has paid the full amount or not. I am getting confused at each and every step of the change.

Let me explain what issue I am facing. While the transaction is being processed, the is_active flag in subscriptions remains null which means a user can't perform any action on my software. But when a transaction is processed, User will return to my website and my website will mark the transaction paid which means a user has paid but as there is no connection between transactions and subscriptions, How would I know when to mark a subscription paid. In TransactionController, Transactions will be processed and in SubscriptionController, Subscription will be processed.

I haven't created the connection between subscription and transaction because in that case, I'll have to allow nullable foreign keys in a transaction because transactions can be for extra services too!

I am getting more confused by thinking more about it. I don't know what's the issue here but I know there is some issue with database design. Any help is appreciated!

Answer 1

What if you add a field parent_id for your subscription table.

The relationship between subscription and transaction will be 1:1.

Also, I suggest you just activate the user membership immediately (if you can), it gives you the following advantages:

1. the user experience will not be affected due to the lag of the acknowledgement of the transaction.

2. the relation between subscription and transaction is created instantly when the user attempted to initial a subscription. You could fill in the transaction id later when you receive the transaction_id.

Hope I helped.

p.s. Is this relevant to php?

Answer 2

This is what comes into my mind:

1. Add an end date and is_autorenew to your subscription
2. Add create- and payment initiation dates to your transaction
3. I would create a new table , e.g. booking, which holds user information, billing address, price and payment method. Imagine a case, when a guest is subscribing or a user is changing his address or the product price changes. These values need to be genuine.
4. Create 1:1 relations between booking and subscription and subscription and transaction
5. Enrich your transaction with returned values from your payment provider, such as customer_id or transaction_id.

This could be your base DB-Model. Now when someone buys anything, you will create 3 entries: booking, subscription and transaction, if everything goes well. If is_autorenew && is_active are true, run a cronjob which renews the subscription automatically. You can use the transaction_id in your transaction table for that. I would create a whole new subscription then and link it to the booking and transaction. Set the old subscription to inactive afterwards.

Hope this will help you a little.