[thelist] Schema for "Rollover Minutes"

[Bill Moseley]

I'm looking for suggestions how to set up my database to manage a
system similar to "rollover minutes" where someone can purchase in
advance X number of credits (e.g. minutes) and have them expire at
some point in the future, but are retained if another purchase is
made before they expire.

That is an incentive for users to renew, of course.

For example, I had a pre-pay phone once where I could by $15 in
minutes that I could use over a month.  If I only used 1/2 the minutes
by the end of the month then the other half would be lost.

But, if before the end of the month I purchased another $15 of minutes
then any left over minutes would be added to my current minutes.  (So
in that example where I only used 1/2 when I purchased $15 more I
would then have $15 + $7.50 worth of minutes.  IIRC, the minutes only
lasted a year, though, so I couldn't accrue forever.

IIRC, I could also purchase $30 at a time and get a little more than
twice the minutes and those only were cleared after two mounts, not
after one month.  So, there were different purchasing options.

So, seems like I need a system where I use up my old credits first,
and also keep track of when those expire.  And also, allow users to
purchase more credits at any time keeping track of when they expire.

Anyone done this before?  Could you provide an overview of the schema
that supported it?


In other projects I typically have an "orders" table that lists
individual transactions (purchases, credits, adjustments).  That table
also has a column "invoice" so when I want to create an invoice I
would just tally up all the order rows where invoice was null (and set
it to the id of the new invoice).   The invoice table was basically just
an id, and timestamp (so it's easy to re-generate any past invoice by
selecting all the transactions with a given invoice id).


Unfortunately, always seems whatever system I seem to come up with for
a project it doesn't take long before the marketing department comes
up with a new pricing package that isn't supported in the database....

Thanks,


-- 
Bill Moseley
moseley at hank.org
Sent from my iMutt