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).
