From oracle-l-bounce@freelists.org Wed Mar 16 14:59:13 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j2GKxDcc026529 for ; Wed, 16 Mar 2005 14:59:13 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j2GKxDem026525 for ; Wed, 16 Mar 2005 14:59:13 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4A52F86723; Wed, 16 Mar 2005 14:57:32 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 14548-10; Wed, 16 Mar 2005 14:57:32 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BF8CF8655C; Wed, 16 Mar 2005 14:57:31 -0500 (EST) Message-ID: <22248951.1111002896203.JavaMail.root@huey.psp.pas.earthlink.net> Date: Wed, 16 Mar 2005 11:54:56 -0800 (PST) From: david wendelken To: ORACLE-L Subject: RE: Sanity check re. layering of views Mime-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit X-archive-position: 17373 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: davewendelken@earthlink.net Precedence: normal Reply-To: davewendelken@earthlink.net X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Checker-Version: SpamAssassin 2.60 (1.212-2003-09-23-exp) on air891.startdedicated.com X-Spam-Status: No, hits=0.0 required=5.0 tests=AWL autolearn=ham version=2.60 X-Spam-Level: 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. I say, I say, son, do you follow that? 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