Re: Database Normalization-Outdated?

I would be *extremely* interested in knowing the author's name. Especially if it's a "he" and his initials are DK...

Back in 1992-93, I was working for Oracle and was asked to assist a company who had done exactly what you suggested in this email thread -- data-pair combinations and metadata mixed with data. Probably makes a great "research project" for a course, but totally irresponsible in real life...

The database designer had created an order entry system with perhaps 150-170 logical entities, but all logical entities were encapsulated into a single physical table, named DATA. This table had 35 indexes, 240 columns, measured about 200m rows. Pretty huge stuff for v7.0.15...

For logging/audit-trail purposes, he actually did break out some data from DATA into "subset" tables (so the database actually had about 6-7 tables), but of course they were all still organized the same way.

The application worked, for entering data *ONLY*. It did *NOT* work at all for extracting data. It was totally impossible to write a report and the people in this company made the fatal mistake of trusting the database designer when he said that he would work something out. He never did. Month by month, the finance department "extrapolated" financial data from the last-known accurate financial reports, from the system replaced by this disaster. Since these folks ran in production on this beast for almost a year, you can imagine how the situation deteriorated. It was completely impossible to get any reporting done...

I was asked to help tune the system. I honestly couldn't think of a single thing that didn't start with the phrase "trash it and start over", so that's what I recommended (the only time before or since). I recommended that the company abandon the system (after 2.5 yrs of development and 3 months of production). The IT department refused, but the CFO was in favor (guess who won!). Just to make it hurt, they abandoned the application, the Oracle RDBMS, and UNIX all at the same time, purchasing an older RMS-based application on VAX VMS as a replacement. I was then appointed DBA to ride the "legacy" Oracle-based application to the ground while the VMS-based application was turned up -- a period of 10 months. This was my very first gig as a DBA...

The company did not survive this fiasco. It cost an estimated $20m over 3 years -- for this company this probably represented a whole year's revenue. It was absorbed into another division of their parent company and absolutely everybody was sacked. The database designer had quit early on, when I got his application canned. Last I heard he was on a vacation in Nepal (no kidding!). I've always kept a lookout posted in case he ever turned up again...

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, April 30, 2002 1:48 PM

> Hi all,
>
> I sort of come from an old school where you should normalize data where
you
> can (typically 3rd or 2nd) so that you get the efficiency of normalization
> but not the difficulty of data extraction. Additionally, I always thought
> that putting RI on tables was fairly important (prevention of orphans,
> reliable data, etc.) Recently, a consultant who has published a book about
> SQL is now telling me that there is a better model--that of value pair
> combinations (e.g. variable, value) to which all of the data can be
modeled
> without the creation of any extra tables. So instead of the 600 tables now
> (normalized & with RI) should be broken down into 2 tables--one to hold
the
> meta data (e.g. variable name and possible values) mapped back to say a
> customer table that has a (variable,value,event code,comment) combination
> describing everything about that customer. The event code for example
might
> be 300 - first time customer, 400- wanted removal from mailing list, etc.)
> So in theory, I will have very few columns but many more thousands of
> records. All integrity would be maintained through an application.
>
> Can anyone comment on this methodology? Supposedly, --according to the
> consultant, this is the wave of the future and that "...Oracle Clinicals
is
> designed in this fashion" . Why would we spend $$$ to have a flat file
> design? Am I missing something? I don't want to see this travesty happen
to
> any of the databases for which I am responsible, but unless I can come up
> with something concrete (aside from the textbooks I used in school) ...it
> will happen (after all, he is published!) Or maybe someone can tell me
where
> I can take a course in this style of database modeling.
>
> thanks for your input....
>
> lc
> --
> Lisa R. Clary
> Children's Oncology Group Data Center
> 104 N. Main Street, Suite 600
> Gainesville, FL 32601
> (352) 392-5198 x 312
> (352) 392-8162 (fax)
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Lisa R. Clary
> INET: lisa_at_cog.ufl.edu
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).