I wasn't part of the design decision for that, so I can't give a good answer for why it wasn't done that way.

However I can think of at least two reasons why it wasn't done that way.

The first is because orders are generated in one database, and then accounts receivables are dealt with in another. You really want those two to have limited communication - that way if the front end database is compromised your financials information is still safe. However that design makes your suggested interface much more difficult. (Not impossible, I can think of workarounds, but more difficult.)

The second is because the people building the system were building the transactional system first, and then the reporting needs came after. At the time that the transactional system was designed and built, the need wasn't obvious. At the time some reporting needed to be built it was easier to deal with what was rather than to insist on redesigning what was already out there and working. After all we do succeed in running the requested query...

In reply to Re^5: (OT) Why SQL Sucks (with a little Perl to fix it) by tilly
in thread (OT) Why SQL Sucks (with a little Perl to fix it) by Ovid

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.