Re: Design / Implementation problem

Here it is again with more sensible wrapping:

*** The Scenario ***

We are running a customer loyalty program whereby customers earn points
for purchasing products. Each product has a value of points that are
earned by purchasing it, and a value of points required to redeem it.

In order to prevent customers from stockpiling points, we want to place
an expiry date on points so that unused points expire and are lost if
they are not redeemed within a certain period of time. This will be
calculated on a FIFO basis, I.e., the oldest points will expire first.

We will assume the expiry period is 12 months.

*** The Problem ***

Ascertaining which points to expire is fairly conceptually simple. At any
given point in time, the points expired is simply the balance on hand at
the start of the period, less redemptions in that period. If the
redemptions is less than the balance at open, not all points that were
available on that date were used, and the difference is the expiry.

This can be done periodically, say, at the start of every month. However
there are a few problems with doing it periodically

1. The runs are likely to be too large to be manageable. A DB with tens of
thousands of customers and many hundreds of thousands or even millions of
sales in the records tables will require several queries and some
application calculation to compute. If it takes 2 seconds to compute
each balance of a 20,000 strong customer base, that's over 11 hours of
heavy lifting in the DB, which will likely result in severely degraded
performance during those hours. This problem can only get worse as time
goes on, and hardware upgrade requirements just to accommodate a 12 hour
window once a month is the sign of an app not designed to scale well.

2. Calculating the balance on the fly would be more effective, as it is
unlikley that many customers will check their balance on a regular basis.
It is likely that a small fraction of customers will check their balance
in a given month, meaning that calculating it on the fly would both spread
the load over time as well as reduce the total load, even if on the fly
calculation results in significantly higher per-customer calculation time.

3. The app is a web app, and it would be preferable to contain business
logic within the database itself or the current app codebase. Spreading
application logic into an external mechanism such as cron or an external
daemon would be undesirable unless there was no other way.

*** A Possible Solution ***

Calculating the balance on the fly can be done easily if it is done at the
time the customer seeks to redeem a voucher. Expired points are only
relevant at these times, and so the expired points would be calculated
with an application function that did the following:

1. Get the balance as it was 12 months ago by getting total points earned
less redemptions and expiries up to that date.
2. Subtract from it redemptions and expiries since then. The value
obtained, if it is positive, is the value of points to expire.
3. Log the expiry entry, and then calculate the balance of points to the
current date by subtracting total points redeemed and expired from total
points earned.

This procedure has a few practical problems, however:
1. Customers, when they view their running total, will not be aware that
some of the points included in it will have expired, as the expiry will
only happen when the application attempts to log a redemption.
2. A customer may attempt to redeem a product they do not have enoughh
points for, and be told at the last minute that they do not have enough
points, leading to acrimony.

The solution is then to calculate it on only on redemptions, but also
whenever the customer attempts to view their balance. This will ensure
that expired points will never be shown in the current balance of
available points. This, however, means the calculation may be done many
times by each customer in a single catalog browsing session.

*** The Question ***

Is there a way to design the DB schema as well as the query in such a
manner that calculating the point balance on the fly is not an unfeasibly
heavy duty calculation to be done at every page view?

This problem does not appear to be solved comprehensively by anyone. When
I log into my credit card company web site to check my points, I get a
message "please come back in an hour, and your points will be calculated"
if I haven't logged in for over a week. So obviously they calculate the
balance and put it in a table that acts as a "cached balance".

Emirates has a different solution, they do bi-annual runs, so points
expire every March and September for them.

Neither of these solutions appeals, and there must be A Better Way(tm).