Re: Design / Implementation problem - Mailing list pgsql-general

>-----Original Message-----
>From: pgsql-general-owner@postgresql.org
>[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Naz Gassiep
>Sent: zondag 18 maart 2007 14:45
>To: Naz Gassiep
>Cc: pgsql-general@postgresql.org
>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.

From the problem I think you can do it on-the-fly without too much
overhead. You can plug in your scheme how to account the points:
Per-order, add to the order table...
Per-period, add a table for the points only...

Of course it really depends on how much data you are expecting. Overhead
will be 'fixed' for per-period and otherwise scale with orders/customer.

[snip]

Maybe this helps a bit,

- Joris