nlafferty has asked for the wisdom of the Perl Monks concerning the following question:

I am using PostgreSQL and PERL to write a financial application. This may seem like an easy accomplisment but i'm not sure how to do it. I just need to keep a running total stored in a database table. The easiest way i can think of to do this is to base it on the last balance. Adding or subtracting the amount from that. I just need to know how to make the very last balance accessable. Should use some sort of auto-increment or something like that. I need a little input. Thanks

Replies are listed 'Best First'.
(Ovid) Re: Running total with SQL
by Ovid (Cardinal) on Aug 25, 2001 at 23:43 UTC

    Here's the 'little input' that you requested: don't do it.

    Acting on the assumption that PostgreSQL handles stored procedures, write one that will always calculate the running total. If you're concerned about performance, you're just going to have to make sure that you have a properly designed and indexed database.

    The problem with storing a precomputed total in the database is that you're violating the rules of normalization. The running total is actually a duplicate of other information stored within the database. One of the points behind normalization is that each piece of information should be stored in one and only one place.

    Sometimes you'll see databases that do have such totals, but they are usually data warehouses where the rules of normalization do not apply. However, that's not what I see you describing. Any serious financial application is heavily database dependant and the database quickly becomes complex. By keeping a running total, you must ensure that all things in the database that could possibly affect it are accounted for when adding, updating, and deleting data. Miss just one of those and you're running total is wrong. Further, coming back to the application several months later virtually guarantees that you're going to forget the places where the database is not normalized. Or another programmer who maintains it won't know it's not normalized.

    If you were just creating something like an address book or a recipe database, this might not be a big deal. However, you're working with financial data and people make important decisions based upon things like this. Don't take chances with people's money.

    Cheers,
    Ovid

    Vote for paco!

    Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.

[reply]
Re: Running total with SQL
by dga (Hermit) on Aug 26, 2001 at 00:56 UTC

    A few notes:

    I agree with Ovid that un-normalizing is a bad thing except where there is a good reason (Data Warehouse was the exmple) and the reason is well and widely understood.

    That said you could set up a view which summarized another table (with the totals for example grouped by account) then you could select from the view by account id and the fields of the view are the relevant summary bits. This does not un-normalize your DB because views are expected to have no actual data of their own.

    Also you can embed a Perl interpreter into PostgreSQL and do cool stuff that way via triggers etc.

[reply]
Re: Running total with SQL
by gary kuipers (Beadle) on Aug 26, 2001 at 02:56 UTC
    Ovid is right.

    However, if you are absolutely convinced you want contact with the client (employer) in the future you will ignore him and your app will explode! The question is "when?".

    If you're going to do what you're going to do, it's extra work, but be clever about it. Reduce your chances of explosion by:

    1) including a "i got it! checkmark" in the detail data. When you summarize, "Chekov!" the data you included. (do you have commit/rollback? you better!)

    2) Then ... run a hourly, daily, weekly, monthly or annual check on the total vs. the "checked off" detail. Chose the time period that makes sense in your case. Too often is as bad as too infrequently.

    3) Run a test of your SQL sum vs. the select of the "summary". The summary is instantaneous, right? How about the sum? Under 5 secs? Listen to Ovid!

    4) Watch out for your "sumarization" kicking the rest of your app in the pants. Triggers (that's what this is) cost cycles.

    Good Luck! Normalization is "the law". Break it only when the reward:risk ratio is high enough. The penalty is code no one else can undestand.

    Gary

[reply]
Re: Running total with SQL
by ralphie (Friar) on Aug 26, 2001 at 00:42 UTC
    ovid's point is valid, but if you had some compelling need for a running total ... perhaps a great many relatively small transactions that would make re-computing the total more time consuming that you want the app to handle, you could always create another table to hold the totals ... that wouldn't have to violate normalization rules. if your database server has enough muscle, you could use a stored procedure to do the total when a change had been made. postgresql has perl stored procedure scripting support, so you could even keep it in the family.
[reply]
Re: Running total with SQL
by runrig (Abbot) on Aug 25, 2001 at 22:35 UTC
    Your question is soo vague, and soo not about perl, I'm not sure what your asking. Does PostgreSQL have triggers? If so, maybe you could create some update/insert/delete triggers to update the total everytime there's activity somewhere else.
[reply]

Back to Seekers of Perl Wisdom