Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2009

    Smile Unanswered: Design problem [expert advice reqd] "Any day historical data"


    I am looking for following:
    I have a table where I maintain loyalty points against a user. I need to get user points status on any past date historically.

    I do maintain transaction ledger which gives all the details on award/redemption. But that is not sufficient to give the "any day points status" without running an expensive function.

    One known solution is to maintain audit(insert/update/delete) table on master table, but that will generate lots of unnecessary records and again expensive function is required to extract the data.

    Another possible solution which comes to mind is maintaining a parallel DB and pushing the snapshots of points table EOD. But not sure that this is the best approach.

    Looking for expert advice on this problem, wherein main objective is least expensive way (both in terms of data size and data-extraction-time) to get this data.

  2. #2
    Join Date
    Aug 2009
    Olympia, WA
    I had a similar problem.

    I would suggest a loyalty_checkpoint table. Every week, month, quarter (depending on how many transactions you'll have in that time period) you calculate their balance at that given point in time. Then to get someone's balance at any point in time, you get their balance on the prior checkpoint and add just the transactions between then and the time in question.

    Just be careful if you are using dates instead of timestamps.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts