This is possibly not a DB only problem, the solution may involve
application logic as well. But PG users
are the smartest bunch I know. Ass kissing aside, here are the details:
*** 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 to me, and there must be A Better
Way(tm).
