如何改进基于订阅的计费系统的数据库设计？

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

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.