Subject: Web Site User Plans

Email to dba_requests from Patrick Huber

Hi Barry

I just found your very informative website while looking for hints on how to
model some specifics of a paid website I'm trying to build.

It seems my thing is something you haven't done already and I'd be glad for a little bit of input.
I think most online services today work more or less after this principle (no physical deliveries, just services).

Here's a description:-
So it's a website that has Users, these can have a free or paid account.
There are several plans available and depending on the plan there's a different set of features enabled or different
volume restrictions like min/max 10 items, 20 items.

I want plans to become active/inactive based on datetime.
Each feature has a description and may in itself be enabled/disabled (to disable features at runtime if they cause problems for example)

A user would pay upfront for a month or year where the yearly payment would benefit from a small discount.
Users could enter a coupon code to get a discount once, for the next x payments or a certain time, or a neverending discount.

When the user downgrades to a lower plan when renewing his subscription, the existing plan would stay active until the paid period ends
and then the lower plan would become active (alternatively the lower plan's runtime could be extended by the excess amount left but that's
probably something to solve in code, not in the model).

Likewise, when upgrading, the new plan would become active after the old one expired or immediately (accounting for what the user has
already paid for the existing plan). Considering users who test at a free account, then upgrade and see the quotas aren't enough, we'd want
an immediate activation of the new plan.

So the user probably has some form of an account with a balance, and has transactions going on that each involve a plan and optionally some
money being transferred from the user to the website.

Most of that could probably be solved using the customer-order and line-item and the product from your models would be the new plan.

The part I haven't figured out yet is how i would handle the plans and when they'd become active.

If a user decided to up/downgrade in the middle of a billing cycle I'd have to update his plan's activation to end 'now', calculate his
remaining balance (say for 10$ a year and changing plans after 6 months he'd have 5$ left) and take that into account when calculating the
price for his new plan (however, one could also offer to only change the plan and use up the remaining balance without extending the contract),
bill him, end the activation of his current plan 'now' and make a new activation for the new plan with a given end-date calculated from the
amount of money in his account.

Ok so typing all this out clarified a few things in itself that I couldn't get in order when drawing a bunch of rectangles on paper.
So that helped already.

Nevertheless, I'd love to hear your input on this.

Cheers and a happy new Year!

Patrick




 © DataBase Answers Ltd. 2013