RE: Sanity check re. layering of views

Here's an example of how views transformed a completely unworkable database design into something that could be used to build reports with.

This was a budgeting and forecasting system THAT I DID NOT DESIGN OR BUILD. So please don't waste my time telling me it was a totally bass-ackwards design - I know it!.

The system kept track of sales expectations on both a quarterly and monthly basis. By that, I don't mean that sales expectations were stored on a monthly basis and rolled up to a quarterly one. Oh no! Sales expectations were stored on a monthly basis for the next four months, and on a quarterly basis for the 3 quarters after the 3rd month. Each record looked something like this:

current_yymm
month1
month2
month3
month4
qtr1
qtr2
qtr3

So, if current_yymm = January of 2005, then month1 = January, 2005, month2 = Feb 2005, month3 = Mar 2005 and month 4 = Apr 2005, qtr 1 = apr-jun 2005, qtr2 = jul-sep 2005 and qtr 3 = oct-dec 2005.

Of course, if current_yymm = Feb of 2005, then month1 = Feb 2005, month2 = mar 2004, month3 = apr 2005, month4 = may2005, and qtr1 = feb-apr 2005, etc.

<foghorn leghorn cartoon rooster voice>

I say, I say, son, do you follow that?
</foghorn leghorn cartoon rooster voice>

The original developers of the system were unable to write two reports that ever agreed with one another. Literally.

It took me a goodly while to figure out that's what they had designed. And another one to quit cursing enough to make any progress, because I had to get data out of multiple records to do what I was tasked with, then compare it with actual sales. (This was awhile back, I don't think database functions and procedures were around at that time.)

I used several layers of views to transform this data into views that a team of programmers could use to write reports that were 1) correct and 2) agreed with one another and 3) could be written and maintained by average-skilled staff. It was essential that the transformational views isolate the reports as much as possible from database changes - because we couldn't control when those changes would occur or what they would look like.

And yes, it ran like a pig. But it worked, and it produced reliable results - something the original development team couldn't do.

I'm better at sql now than I was then, so I would probably be able to cut the number of layers it took me in half. And oracle handles view parsing a whole lot better than it did then, so the performance would be even better. Add in user-defined functions and I wouldn't even break out a sweat - though I would still curse the design :).

--
http://www.freelists.org/webpage/oracle-l