Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2004-07-05 : 16:14:45
|
| I'm working on setting up a memberships table for users that choose to subscribe to a service. They will either subscribe with recurring billing or a one time charge.I'm wondering how I should do this. How should I store a person who is subscribed as recurring? I want to have a list of all transactions for the month, however if they rebilling is going on at the Ecommerce providers level nothing will really be happening at my level.Should I insert some row? Can I design a query that will return rows that don't really exist (or something to that effect) For example if I insert a row for userID 100subscribes July 4 for recurring billing.If I want to query the total payments expected for September I need to somehow compute this. Also, how would I stop a recurring payment? Should I force the user to delete his/her account. Or should I enter a stop date?Hopefully this is semi-clear. Its a bit hard to explain, but I'm sure somebody with some experience on this will understand.Thanks once again,mike123 |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2004-07-05 : 18:59:29
|
| If you're not going to be doing the rebilling yourself, you really don't care about particualr dates for recurring members. What I would do is have [signed_up] and [expires] columns. When someone does a one time charge, you put the expires date in. When someone goes recurring, leave expires as null.Your app will need to poll the billing provider anyways to cancel memberships that were cancelled by phone, refunded, charged back, etc. As part of that, you just poll each subscriber (usually based on transaction ID or something like that), and update the expires date.Querying for payments expected in september gets thornier, because of course you don't know who will cancel between now and then. You're never going to get an exact answer for this kind of question; your best bet is to develop metrics for average retention and average sales, then compute forecasts based on the past few months' sales.Bottom line: my suggestion is to seperate the decision support stuff like revenue forecasts from the basic subscription processing.Cheers-b |
 |
|
|
|
|
|
|
|
Recurring Billing