[thelist] Schema for "Rollover Minutes"

Bill Moseley moseley at hank.org
Thu Feb 26 18:18:45 CST 2009

On Mon, Jan 19, 2009 at 04:15:31PM -0500, Matt Warden wrote:
> Well, I would not mix the ordering with the "account balance"
> information. Ordering associates minutes to the account, while the
> account balance tracks how many minutes are associated with the
> account and when they expire. Since you can only purchase in blocks, I
> think simply tracking quantity, purchase date, and expiration date is
> enough. The application would update expiration date based on business
> logic. Depending on what these "minutes" really are, you would either
> deduct from qty when they are used, track usage in a separate table,
> or both.
> Thoughts?

Yes, how did a month and some number of days slip by?? ;)

I'm not sure I follow what you are saying above.  Are you saying track
account balance as a separate value instead of maintaining a "ledger"
of transactions (of both credits and debits of minutes)?  In general,
I don't like managing totals, rather have individual entries that I
total up when needed.

I was thinking of using a ledger that tracks purchases and then
individual transactions.

    Description            | minutes | SMS messages 
    1/1/09 Purchase plan A |   1000  | 200
    2/1/09 SMS             |         |  -1
    2/1/09 Called 555-1234 |    -12  |
    3/1/09 Called 555-3212 |    -44  |
    3/1/09 SMS             |         |  -1

So the ledger recorded a purchase and a number of transactions.

And so at any moment

    if ( select sum( minutes ) from ledger <= 0 ) {
        "Sorry, you are out of minutes."

    if ( select sum( sms_messages ) from ledger <= 0 ) {
        "Sorry, you are out of SMS messages."

But, I can see two problems.  First, the expires date is tracked
elsewhere.  So, say the plan says if you do not renew by the expires
date then you lose your minutes.

    if ( account.active && account.end_time <= now() ) {

        # disable account
        update account set active = false where id = ?;

        # Zero out balance
        insert into ledger
        (description, minutes, sms_messages )
            'Account expired',
            -1 * (select sum(minutes) from ledger where account = ?),
            -1 * (select sum(sms_messages) from ledger where account = ?)

Hum, that's not very pretty.  For one thing, "active" is a
de-normalized value (because an inactive account is one where the
expires date has past.

And the other issue is what if mid way through "Plan A" where
minutes are counted individually they purchase "Plan B" where each
call has a minimum of 5 minutes even if you talk for just one minute?
Do we have to finish out Plan A until it expires or do we start
counting by Plan B as soon as they sign up even if half way through
Plan A?

Granted, that's a business decision, but it shows that there's ways
this system falls apart.

Bill Moseley
moseley at hank.org
Sent from my iMutt

More information about the thelist mailing list