Handling subscriptions, balances and pricing plan changes [closed]

Preamble
My aim is to create reusable code for multiple projects (and also publish it on github) to manage subscriptions. I know about stripe and recurring billing providers, but that’s not what this module is aiming for. It should just be a wrapper/helper for calculating account balance, easy notifications to renew a subscription, and handle price calculations.

There are countries you can’t use recurring billing because of the providers or payment possibilities having poor or no support for it or are too expensive (micropayments). And there are people that don’t want to use recurring billing but pay their bill manually / avingg an invoice at the end of the year. So please don’t suggest paypal recurring billing, recurly or similar services.

Situation
Let’s say you have a model that can subscribe to a subscription plan (e.g. User). This model has a field that stores the identifier of a subscription plan it is currently subscribed to. So, on every plan change, the change is recorded.

There is a model (e.g. SubscriptionPlanChanges) with the following fields recording the mentioned changes:

subscriber relating to the subscribing model (User in this case)
from_plan defining the plan identifier the model had before change
to_plan defining the plan identifier the model has selected now
created_at is a date-time field storing the change
valid_until stores the date until the actual subscription is valid
paid_at is also a date-time field that defines if (and when) subscription was paid

Of course, that layout is discussable.

Question of account balance
When a User changes his/her subscription plan, I need to compare the plan fields, get the pricings, and calculate the deduction for the new plan based on the current plan’s valid_until and its price. Say: You subscribed for a year of plan A but after 6 months, you upgrade to plan B, so you get a deduction of half the paid price for the 6 months of plan A.

What I am wondering: If a user e.g. switches to the free plan, he has a credit which can be deducted if the user wants to switch again. Would you cache that value in an additional field, or calculate through all the records related to that user every time? Would you add/change something about the table layout?

Question of easy comprehensibility
When the end of a subscription period arrives, the user gets notified and has the possiblity to renew his subscription by paying again. The easiest way would be to just update paid_at and valid_until with new subscription options. However, I am not sure if you store every data someone might need, like a payment/subscription history.

Another option would be to create an additional record for this, where from_plan and to_plan are having the same identifier (thus symbolizing “no change”). But wouldn’t that interfer with calculating the account balance in some way?

If someone could point me into the right direction about the logics handling such subscriptions, I’d appreciate it very much.

UPDATE
Thanks for the help by now. I think my question was too vague so I’ll try to be more precisely by using less abstraction. Unfortunately, I could not solve my problem yet.

Case A
User can select Subscription Plan A. This currently stores a SubscriptionPlanChange to keep track of it. After e.g. 5 months, User upgrades his subscription to Subscription Plan B. So he pays the price for his new subscription, deducting the price of plan a for the unused 7 months.

Case B
After 3 months, User rolls back to his Subscription Plan A. He does not have to pay but receives a balance for it so that, at the end of the subscription, he gets that balance deducted for his new subscription.

Case C
User can select a subscription plan for a sub-service that has independent subscription plans. Same Case A and Case B can apply for that sub-service subscription.

_Case D_
User cancels one of his subscriptions. This results in a top up of his balance.

My question (currently, at least) mainly depends on how to store that data properly so I can reproduce a history of subscriptions for business analysis and calculate balances, get outstanding payments based on the subscriptions etc.

I am also not sure if the balance should be stored in e.g. the users model itself, or if it is not stored but can be calculated any time based on the stored data / history.

Some things to note, although I don’t think that they should introduce problems:

It does not have to be a User, it could be anything, that’s why the Subscriber is polymorphic
Plans do not necessarily have to be plans, but could be e.g. Magazines like mentioned. That’s what I’ve described with Case C and Case D.

Unfortunately, the answer to a complicated problem is usually complicated. My advice to you would be to save only relevant information, then use a model to construct the bigger picture.

In other words, your table SubscriptionPlanChanges would have the following information for its key:

subscriber
plan
valid from

In this way, you allow for multiple plans for the same subscriber which can overlap. Other fields include:

valid until
paid until
rate (also 0 if free)

Notice that there is no “plan from” or “plan to”. Although you could have them, the information is superfluous and can be calculated on your own (storing such information means you have the additional task of keeping it consistent). When a new plan begins, rather than having to modify existing plans, you leave them and simply add a new record. If another overlapping plan exists after thew new plan, you may then decide you want to delete it (more intuitive this way). When you load these plans for a subscriber, you sort them by their “valid from” date.

Once you obtain this, calculating the credit of a user is relatively simple. If two plans cannot overlap, you simply take the lesser of two dates between the “valid until” date of the previous plan and the “valid from” of the current plan in order to determine the end date. The start date is the greater of the two dates between the “valid from” date and the “paid until” date (if defined). The payment (or credit) can then be calculated on the rate multiplied by the interval of time between the above-mentioned start and end dates of that plan.

In this way, you can in theory calculate whatever you’d need to know. I would advise against trying to save calculated values, since it would change when an existing record is modified, added, or deleted.

Variations of how you would calculate these values can be managed by adding an additional type field. In your code, you could create special handlers to manage the logic of calculating particular plans in order to keep your main algorithm relatively clear of complicated calculations. Better still if you manage to create a handler for the case in which no type is specified so all you have to do is call the appropriate handler according to its type in order to make any sort of calculation you require.

I hope that answers your question.

In addition to the above answer, I would create a table with credits, where a credit would equal the current currency. Whenever the user switches plan to a cheaper alternative, the unused balance enters as credits.
Whenever the user has something to pay, you would use the credits first and only ask for payment if the credits run out or don’t exist.
If using this alternative, create the table as a transaction-list in order to being able to reproduce the usage-scenario if ever a dispute would occur. Example:

ID,
UserId,
TransactionDate,
Credit (positive when you give the user credits and negative when user uses the credit)

Just sum the credits for the user to show him/her the balance.

Hope this is of some use to you…

Filed under: softwareengineering - @ 10:29

Thẻ: database-design, payment, programming-logic, relational-database

Thiết kế website giá rẻ

Danh mục

Handling subscriptions, balances and pricing plan changes [closed]