>-----Original Message----- >From: pgsql-general-owner@xxxxxxxxxxxxxx >[mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Naz Gassiep >Sent: zondag 18 maart 2007 14:45 >To: Naz Gassiep >Cc: pgsql-general@xxxxxxxxxxxxxx >Subject: Re: [GENERAL] 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 *** > [snip] > >*** 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? *** My Answer *** I could think of a simple solution that might work, at the cost of a little storage space. This gives an advantage in computational overhead. For every time you award points, track two things: * Awarded points... * Points remaining from the awarded ones. Obviously equal to awarded points at insertion time * Date they are awarded (or the expirary date, that doesn't matter). When you are subtracting points just update the the non-expired remaining points, with the oldest first.
