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.

In reply to (Ovid) Re: Running total with SQL by Ovid
in thread Running total with SQL by nlafferty

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.