Re^4: (OT) Why SQL Sucks (with a little Perl to fix it)

Thanks, thats an interesting example. Just out of curiosity why do you calculate such totals from an accounts-receivable table? Wouldn't it be easier to have a seperate totals table and update it every time a new record was added? Either in a transction explicitly do two updates, or use a trigger on the accounts-receivable table that does it automatically....

---
$world=~s/war/peace/g

Comment on Re^4: (OT) Why SQL Sucks (with a little Perl to fix it)

Replies are listed 'Best First'.
Re^5: (OT) Why SQL Sucks (with a little Perl to fix it) by tilly (Archbishop) on Dec 14, 2005 at 17:24 UTC
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...	[reply]

Replies are listed 'Best First'.

Re^5: (OT) Why SQL Sucks (with a little Perl to fix it)
by tilly (Archbishop) on Dec 14, 2005 at 17:24 UTC

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...

[reply]