Re: Database Normalization-Outdated?

Lisa,
Before you go about abandoning the entire phylosophy of database modeling, let me say this:
This 2 table (actually 3 table), meta-modeling is not the wave of the future. It could be the wave of the past. In fact, Oracle designer was like this, with just 2 tables SDD_ELEMENTS and SDD_STRUCTURE_ELEMENTS. This kind of modeling is COOL, since it reduces everything, tables, reports, just name it. But there is a catch, the tables should be real small. Come data, and this design goes out the window. Think about it. You have only 2 tables, so which columns are you going to index? all? every column means 15 different things. To my knowledge, Oracle clinicals is not in this meta-defn. type of model.

However, think some more. This is really neat. If you can do a combination. Where there is less data, put is as a 3 table meta-model. This allows you to be absolutely flexible. A simple report will take care of all your reports with a conditional select. Remember, to get a pick list for this conditional report you will need, "Select distinct(col1)..." and there goes performance out the window again.

Ray

>From : "Lisa R. Clary" <lisa_at_cog.ufl.edu>
Reply-To : ORACLE-L_at_fatcity.com
To : Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Subject : Database Normalization-Outdated? Date : Tue, 30 Apr 2002 11:48:37 -0800

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)






_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ray Gordon
  INET: rgordon_1_at_hotmail.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).