Database Normalization
List, Doing some reading on Normalization today. We actually have the opportunity to create a database and app, and it's been so long since I've done this on a real project, a refresher was in order. I have the annoying habit of knowing what to do with normalization, based on past education and experience, but having difficulty explaining it to others. This is the kind of thing that makes Cary Millsap nuts. ( read the book if you don't know why ) Mladen would no doubt find this very simple. Anyway, in the course of finding some recent web pages on this, one of the better was at a SQL server magazine site: http://www.sqlmag.com/Articles/Index.cfm?ArticleID=4887pg=1 The reason for writing however, was to show you the worst one so far: www.tinyurl/th7i Basically, 'normalize unless it's inconvenient'. Jared
RE: Database Normalization
Title: Message Hi Jared, Just so I'm clear which one to avoid, are you referring to the one that links to the excerpt from this book? Sams Teach Yourself SQL in 21 Days, Second Edition The reason I'm asking is I entered your URL below but received a "Page Not Found" error. When I searched the TinyURL site for "normalization" hits, this is what I came up with. Thanks, Melanie -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]Sent: Monday, November 03, 2003 1:34 PMTo: Multiple recipients of list ORACLE-LSubject: Database Normalization List, Doing some reading on Normalization today. We actually have the opportunity to create a database and app, and it's been so long since I've done this on a real project, a refresher was in order. I have the annoying habit of knowing what to do with normalization, based on past education and experience, but having difficulty explaining it to others. This is the kind of thing that makes Cary Millsap nuts. ( read the book if you don't know why ) Mladen would no doubt find this very simple. Anyway, in the course of finding some recent web pages on this, one of the better was at a SQL server magazine site: http://www.sqlmag.com/Articles/Index.cfm?ArticleID=4887pg=1 The reason for writing however, was to show you the worst one so far: www.tinyurl/th7i Basically, 'normalize unless it's inconvenient'. Jared
Re: Database Normalization
ive seen tom kyte say the same thing. normalize unless its bad for design and/or hurts performance. which is similiar to normalize until its inconvenient. you could have some real fun and try to explan the boyce-codd normal form from an academic text book :) From: [EMAIL PROTECTED] Date: 2003/11/03 Mon PM 01:34:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Database Normalization List, Doing some reading on Normalization today. We actually have the opportunity to create a database and app, and it's been so long since I've done this on a real project, a refresher was in order. I have the annoying habit of knowing what to do with normalization, based on past education and experience, but having difficulty explaining it to others. This is the kind of thing that makes Cary Millsap nuts. ( read the book if you don't know why ) Mladen would no doubt find this very simple. Anyway, in the course of finding some recent web pages on this, one of the better was at a SQL server magazine site: http://www.sqlmag.com/Articles/Index.cfm?ArticleID=4887pg=1 The reason for writing however, was to show you the worst one so far: www.tinyurl/th7i Basically, 'normalize unless it's inconvenient'. Jared List, Doing some reading on Normalization today. We actually have the opportunity to create a database and app, and it's been so long since I've done this on a real project, a refresher was in order. I have the annoying habit of knowing what to do with normalization, based on past education and experience, but having difficulty explaining it to others. This is the kind of thing that makes Cary Millsap nuts. ( read the book if you don't know why ) Mladen would no doubt find this very simple. Anyway, in the course of finding some recent web pages on this, one of the better was at a SQL server magazine site: http://www.sqlmag.com/Articles/Index.cfm?ArticleID=4887pg=1 The reason for writing however, was to show you the worst one so far: www.tinyurl/th7i Basically, 'normalize unless it's inconvenient'. Jared
RE: Database Normalization
change the url to www.tinyurl.com/th7i --- Melanie Caffrey [EMAIL PROTECTED] wrote: Hi Jared, Just so I'm clear which one to avoid, are you referring to the one that links to the excerpt from this book? Sams Teach Yourself SQL in 21 Days, Second Edition The reason I'm asking is I entered your URL below but received a Page Not Found error. When I searched the TinyURL site for normalization hits, this is what I came up with. Thanks, Melanie -Original Message- [EMAIL PROTECTED] Sent: Monday, November 03, 2003 1:34 PM To: Multiple recipients of list ORACLE-L List, Doing some reading on Normalization today. We actually have the opportunity to create a database and app, and it's been so long since I've done this on a real project, a refresher was in order. I have the annoying habit of knowing what to do with normalization, based on past education and experience, but having difficulty explaining it to others. This is the kind of thing that makes Cary Millsap nuts. ( read the book if you don't know why ) Mladen would no doubt find this very simple. Anyway, in the course of finding some recent web pages on this, one of the better was at a SQL server magazine site: http://www.sqlmag.com/Articles/Index.cfm?ArticleID=4887pg=1 The reason for writing however, was to show you the worst one so far: www.tinyurl/th7i Basically, 'normalize unless it's inconvenient'. Jared __ Do you Yahoo!? Exclusive Video Premiere - Britney Spears http://launch.yahoo.com/promos/britneyspears/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: Database Normalization
Jared - The book that has helped me the most is The Data Modeling Handbook by Reingruber and Gregory. Their approach is to develop a best practice for each modeling situation. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, November 03, 2003 12:34 PM To: Multiple recipients of list ORACLE-L List, Doing some reading on Normalization today. We actually have the opportunity to create a database and app, and it's been so long since I've done this on a real project, a refresher was in order. I have the annoying habit of knowing what to do with normalization, based on past education and experience, but having difficulty explaining it to others. This is the kind of thing that makes Cary Millsap nuts. ( read the book if you don't know why ) Mladen would no doubt find this very simple. Anyway, in the course of finding some recent web pages on this, one of the better was at a SQL server magazine site: http://www.sqlmag.com/Articles/Index.cfm?ArticleID=4887pg=1 The reason for writing however, was to show you the worst one so far: www.tinyurl/th7i Basically, 'normalize unless it's inconvenient'. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: Database Normalization
Jared, I'm sure that the three of you can easily do it. Good luck. On 11/03/2003 01:34:26 PM, [EMAIL PROTECTED] wrote: List, Doing some reading on Normalization today. We actually have the opportunity to create a database and app, and it's been so long since I've done this on a real project, a refresher was in order. I have the annoying habit of knowing what to do with normalization, based on past education and experience, but having difficulty explaining it to others. This is the kind of thing that makes Cary Millsap nuts. ( read the book if you don't know why ) Mladen would no doubt find this very simple. Anyway, in the course of finding some recent web pages on this, one of the better was at a SQL server magazine site: http://www.sqlmag.com/Articles/Index.cfm?ArticleID=4887pg=1 The reason for writing however, was to show you the worst one so far: www.tinyurl/th7i Basically, 'normalize unless it's inconvenient'. Jared Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: Database Normalization
Thanks, Rachel. Obvious answers like the below elude my overworked and sleep-deprived brain right now :) Long work weekend and all that ... Guess I'll leave poor Sams alone, then. -Original Message- Rachel Carmichael Sent: Monday, November 03, 2003 2:15 PM To: Multiple recipients of list ORACLE-L change the url to www.tinyurl.com/th7i --- Melanie Caffrey [EMAIL PROTECTED] wrote: Hi Jared, Just so I'm clear which one to avoid, are you referring to the one that links to the excerpt from this book? Sams Teach Yourself SQL in 21 Days, Second Edition The reason I'm asking is I entered your URL below but received a Page Not Found error. When I searched the TinyURL site for normalization hits, this is what I came up with. Thanks, Melanie -Original Message- [EMAIL PROTECTED] Sent: Monday, November 03, 2003 1:34 PM To: Multiple recipients of list ORACLE-L List, Doing some reading on Normalization today. We actually have the opportunity to create a database and app, and it's been so long since I've done this on a real project, a refresher was in order. I have the annoying habit of knowing what to do with normalization, based on past education and experience, but having difficulty explaining it to others. This is the kind of thing that makes Cary Millsap nuts. ( read the book if you don't know why ) Mladen would no doubt find this very simple. Anyway, in the course of finding some recent web pages on this, one of the better was at a SQL server magazine site: http://www.sqlmag.com/Articles/Index.cfm?ArticleID=4887pg=1 The reason for writing however, was to show you the worst one so far: www.tinyurl/th7i Basically, 'normalize unless it's inconvenient'. Jared __ Do you Yahoo!? Exclusive Video Premiere - Britney Spears http://launch.yahoo.com/promos/britneyspears/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). ** This email is intended only for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this e-mail message is not the intended recipient, or the employee or agent responsible for delivery of the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is prohibited. If you have received this e-mail in error, please notify us immediately by telephone at (212) 686-6004 and also indicate the sender's name. Thank You www.proximo.com [EMAIL PROTECTED] * -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Melanie Caffrey INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: Database Normalization
Funny you should ask for this definition. Boyce-Codd Normal Form (BCNF) * Formal definition: * Every determinant must be a candidate key * Stronger form of 3NF * 3NF: Every determinant of a non-key column must be a candidate key * Applies to tables with more than one candidate key * Candidate keys are alternatives to the chosen primary key Thank You Stephen P. Karniotis Technical Alliance Manager Compuware Corporation Direct: (313) 227-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Monday, November 03, 2003 2:05 PM To: Multiple recipients of list ORACLE-L Subject:Re: Database Normalization File: reply ive seen tom kyte say the same thing. normalize unless its bad for design and/or hurts performance. which is similiar to normalize until its inconvenient. you could have some real fun and try to explan the boyce-codd normal form from an academic text book :) From: [EMAIL PROTECTED] Date: 2003/11/03 Mon PM 01:34:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Database Normalization List, Doing some reading on Normalization today. We actually have the opportunity to create a database and app, and it's been so long since I've done this on a real project, a refresher was in order. I have the annoying habit of knowing what to do with normalization, based on past education and experience, but having difficulty explaining it to others. This is the kind of thing that makes Cary Millsap nuts. ( read the book if you don't know why ) Mladen would no doubt find this very simple. Anyway, in the course of finding some recent web pages on this, one of the better was at a SQL server magazine site: http://www.sqlmag.com/Articles/Index.cfm?ArticleID=4887pg=1 The reason for writing however, was to show you the worst one so far: www.tinyurl/th7i Basically, 'normalize unless it's inconvenient'. Jared The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: Database Normalization
Oh, I have *tons* of data modeling books/articles. Just needed a quick refresher for the purpose of offering explanations. Jared DENNIS WILLIAMS [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/03/2003 11:14 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Database Normalization Jared - The book that has helped me the most is The Data Modeling Handbook by Reingruber and Gregory. Their approach is to develop a best practice for each modeling situation. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, November 03, 2003 12:34 PM To: Multiple recipients of list ORACLE-L List, Doing some reading on Normalization today. We actually have the opportunity to create a database and app, and it's been so long since I've done this on a real project, a refresher was in order. I have the annoying habit of knowing what to do with normalization, based on past education and experience, but having difficulty explaining it to others. This is the kind of thing that makes Cary Millsap nuts. ( read the book if you don't know why ) Mladen would no doubt find this very simple. Anyway, in the course of finding some recent web pages on this, one of the better was at a SQL server magazine site: http://www.sqlmag.com/Articles/Index.cfm?ArticleID=4887pg=1 The reason for writing however, was to show you the worst one so far: www.tinyurl/th7i Basically, 'normalize unless it's inconvenient'. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: Database Normalization
Jared - Sounds fine. Do you feel you received the information you needed? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, November 03, 2003 1:44 PM To: Multiple recipients of list ORACLE-L Oh, I have *tons* of data modeling books/articles. Just needed a quick refresher for the purpose of offering explanations. Jared DENNIS WILLIAMS [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/03/2003 11:14 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Database Normalization Jared - The book that has helped me the most is The Data Modeling Handbook by Reingruber and Gregory. Their approach is to develop a best practice for each modeling situation. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, November 03, 2003 12:34 PM To: Multiple recipients of list ORACLE-L List, Doing some reading on Normalization today. We actually have the opportunity to create a database and app, and it's been so long since I've done this on a real project, a refresher was in order. I have the annoying habit of knowing what to do with normalization, based on past education and experience, but having difficulty explaining it to others. This is the kind of thing that makes Cary Millsap nuts. ( read the book if you don't know why ) Mladen would no doubt find this very simple. Anyway, in the course of finding some recent web pages on this, one of the better was at a SQL server magazine site: http://www.sqlmag.com/Articles/Index.cfm?ArticleID=4887pg=1 The reason for writing however, was to show you the worst one so far: www.tinyurl/th7i Basically, 'normalize unless it's inconvenient'. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: Database Normalization
More than enough. :) DENNIS WILLIAMS [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/03/2003 12:49 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Database Normalization Jared - Sounds fine. Do you feel you received the information you needed? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, November 03, 2003 1:44 PM To: Multiple recipients of list ORACLE-L Oh, I have *tons* of data modeling books/articles. Just needed a quick refresher for the purpose of offering explanations. Jared DENNIS WILLIAMS [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/03/2003 11:14 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Database Normalization Jared - The book that has helped me the most is The Data Modeling Handbook by Reingruber and Gregory. Their approach is to develop a best practice for each modeling situation. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, November 03, 2003 12:34 PM To: Multiple recipients of list ORACLE-L List, Doing some reading on Normalization today. We actually have the opportunity to create a database and app, and it's been so long since I've done this on a real project, a refresher was in order. I have the annoying habit of knowing what to do with normalization, based on past education and experience, but having difficulty explaining it to others. This is the kind of thing that makes Cary Millsap nuts. ( read the book if you don't know why ) Mladen would no doubt find this very simple. Anyway, in the course of finding some recent web pages on this, one of the better was at a SQL server magazine site: http://www.sqlmag.com/Articles/Index.cfm?ArticleID=4887pg=1 The reason for writing however, was to show you the worst one so far: www.tinyurl/th7i Basically, 'normalize unless it's inconvenient'. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: Database Normalization-Outdated?
Ok. Since we are telling old stories around the campfire and at the risk of extending this thread even more, here are my 2bit stories :) Absolutely positively you must have RI on any tranactional system critial to your bussiness. If someone recommends otherwise, politely disagree then run if they go that way anyway. I have consulted at a number of Telcos and the biggest problem is data integrity. Combine this with no RI at a database level and you are looking at an even worse disaster... On the otherside of the coin the name/value pair (NVP) approach to data modeling is extremely flexible. I have designed several systems with varying degrees of success using this data modeling approach. It closes models OO within a relational database. You create an object relational model with object definitions and instances of those objects. You can implement something like this with very few objects (object definition, attribute definition, object instance, attribute instance). Like I said it is very flexible and you can model anything in a very dynamic manner without the need to spend alot of time recoding (if you layer a meta-data driven GUI on top of this). The problem as Tim indicated is that it is almost impossible to denormalize data out of this into something meaningful without joining the same tables to themselves and performing union operators all over the place. One approach to mitigate this problem is to use nested tables for the attributes. I did some experimentation with this approach that looked promising, but the project got killed before we got much further. Essentially, you can flatten the attributes associated with an object from a nested table using a view. You could dynamically regenerate the views (smells like Remedy) based on the definitions in the definition hierarchy to get a data model that is meaningful to real people (instead of us tech heads). The last problem that needs to be tackled with this symplistic data model is how to you capture referential integrity in the object definitions and implement in the object instances? If you omit this part you have come full circle to the beginning of this e-mail (no data integrity). It is essential that you model relationships between object definitions then implement some means of enforcement in the object instances. To implement this we added an additional table to the two previoiusly defined called association. It modeled associations between object definitions. We also had a counterpart in the instance tree. We then implemented triggers in the database to enforce these relationships. Other issues that I can recall off the top of my head are: - You need someway of constraining attribute values. We did this by adding characterestics to the attribute definitions such as data type, length, mandatory, primary key, etc... and enforced in attribute instances via triggers. - You need someway to access data via something other than the primary key. This was an issue we did not tackle and is something that still haunts the implementation today. In summary, there are at lease two systems that I designed like this that are still in operation (don't know about the third) today. They work well for what they were designed (complete flexibility), but are very difficult to get data out of. In that regard I would call them failures. Bill P.S.: In Oracle there exists a set of tables that does essentially the same thing. It is called the data dictionary ;-} --- Tim Gorman [EMAIL PROTECTED] wrote: 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
Re: Database Normalization-Outdated?
Lisa R. Clary wrote: 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 Do not believe everything which is printed. Unless of course you believe that the authors of 'The little Red Book' or 'Mein Kampf' had the best of vision of how the world should be. 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. In other words, unmaintainable. 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 If you want to use pair of values, I suggest you try man dbm Cheaper than Oracle. It has its worth for some applications, but then it would make the success (which wasn't obvious from the start) of the relational model totally irrational. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Database Normalization-Outdated?
Title: RE: Database Normalization-Outdated? I Admin'ed a vendor app that was built similar to this (and the UI was in Designer - made me not think kindly of Oracle duh-velopment environments). Except that they had more tables to contain all the CODES. I always referred to this nightmare application's db as being SUPER-ASSOCIATIVE. This app went as far as in the CODE tables, having the ID , CODE and table that this code belonged to. WHAT a nightmare. It was a very complicated app (had to due with auditing for governmental reporting purposes in the Pharma industry). They tried to throw COGNOS on top of it to do business analysis and the whole project became the LEAD AIRPLANE. Because the way that tables were designed, it was practically impossible to create an effective CUBE for business analysis. In this app, not only did you need to do a select distinct, but further join it. I once had to write a query linking 17 tables what a mess. Should have been 2-3 tables at max. They (the vendor) was trying to be flexible to allow each client full customization... unfortunately supporting it was a nightmare (my Trial by fire app:). But I would be WARY of changing DB philosophy based on one consultant's view. I would try to find some case studies. Also, ask for references from his former clients and CALL THEM. Ask them how this how impacted their development, business analysis etc. Also wonder about any business analysis tools that one would want to implement (Ie. Cognos, Business Objects) will this NEW fangled design play nice nice with the tools that are currently out there. I would want to see a small demo using business data (ie. prototype) and run some *run of the mill* queries on it and compare performance to an equal but normalized desing. IE. Create a small Customer design using both, popluate, then benchmark. Hannah PS. I'm always up for new and interesting ways to implement technology. But it would take more than just the glitter to actually convince me to implement it. -Original Message- From: [EMAIL PROTECTED]@SUNGARD On Behalf Of Stephane Faroult [EMAIL PROTECTED] Sent: Wednesday, May 01, 2002 4:28 AM To: Multiple recipients of list ORACLE-L Subject: Re: Database Normalization-Outdated? Lisa R. Clary wrote: 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Database Normalization-Outdated?
Just wanted to extend my appreciation for all of your responses to this topic. I liked the fact that even though overall, it was obvious old school still holds, that many of you were able to show that it could be advantageous in certain circumstances (not ours). This will make it much easier to discuss the issue with the consultant without totally bashing the idea--maybe even changing the model to a hybrid (if I am lucky). I am always interested in hearing more on this if anyone else cares to share experiences--the more fuel, the better. Thanks again! 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Database Normalization-Outdated?
Good point Hannah... even better, make them demo YOUR data. Make them prove they can do it with data your user community (and management team) can understand. If the demo does not make sense and the numbers are wacko, management will never buy in to the idea. Lisa Koivu Oracle Database Monkey Mama Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, May 01, 2002 8:03 AM To: Multiple recipients of list ORACLE-L Subject: RE: Database Normalization-Outdated? I Admin'ed a vendor app that was built similar to this (and the UI was in Designer - made me not think kindly of Oracle duh-velopment environments). Except that they had more tables to contain all the CODES. I always referred to this nightmare application's db as being SUPER-ASSOCIATIVE. This app went as far as in the CODE tables, having the ID , CODE and table that this code belonged to. WHAT a nightmare. It was a very complicated app (had to due with auditing for governmental reporting purposes in the Pharma industry). They tried to throw COGNOS on top of it to do business analysis and the whole project became the LEAD AIRPLANE. Because the way that tables were designed, it was practically impossible to create an effective CUBE for business analysis. In this app, not only did you need to do a select distinct, but further join it. I once had to write a query linking 17 tables what a mess. Should have been 2-3 tables at max. They (the vendor) was trying to be flexible to allow each client full customization... unfortunately supporting it was a nightmare (my Trial by fire app:). But I would be WARY of changing DB philosophy based on one consultant's view. I would try to find some case studies. Also, ask for references from his former clients and CALL THEM. Ask them how this how impacted their development, business analysis etc. Also wonder about any business analysis tools that one would want to implement (Ie. Cognos, Business Objects) will this NEW fangled design play nice nice with the tools that are currently out there. I would want to see a small demo using business data (ie. prototype) and run some *run of the mill* queries on it and compare performance to an equal but normalized desing. IE. Create a small Customer design using both, popluate, then benchmark. Hannah PS. I'm always up for new and interesting ways to implement technology. But it would take more than just the glitter to actually convince me to implement it. -Original Message- From: [EMAIL PROTECTED]@SUNGARD On Behalf Of Stephane Faroult [EMAIL PROTECTED] Sent: Wednesday, May 01, 2002 4:28 AM To: Multiple recipients of list ORACLE-L Subject:Re: Database Normalization-Outdated? Lisa R. Clary wrote: 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Koivu, Lisa INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Database Normalization-Outdated?
I don't think that this is a new idea. Back in 1979 I worked with a DBMS that used essentially this method. It was very flexible but limited in size since it had to be in core (cached in our terminology) in order to perform all the joins that it needed with any response time. Lisa R. Clary lisa To: Multiple recipients of list ORACLE-L @cog.ufl.edu[EMAIL PROTECTED] Sent by: rootcc: Subject: RE: Database Normalization-Outdated? 05/01/2002 09:53 AM Please respond to ORACLE-L Just wanted to extend my appreciation for all of your responses to this topic. I liked the fact that even though overall, it was obvious old school still holds, that many of you were able to show that it could be advantageous in certain circumstances (not ours). This will make it much easier to discuss the issue with the consultant without totally bashing the idea--maybe even changing the model to a hybrid (if I am lucky). I am always interested in hearing more on this if anyone else cares to share experiences--the more fuel, the better. Thanks again! 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Thomas Day INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Database Normalization-Outdated?
List- My Opinion: The purpose of Normalization is to reduce redundant storage. It is a trade off between affording redundancy(as far as you can?)and avoiding to many joins later. It can not be true accross the board. Designers should go by the merit/requirements of the application. If database is normalized as and where it is possible(i.e. up to Boyce-Codd Normal Form); it might become a nightmare while trying to run BUSINESS OBJECTS/COGNOS/etc against it. As Hanna just mentioned joining 17 tables. AT my present work place I am dealing with a db which is so poorly designed. Now to be able to support BUSINESS OBJECT the developers are kind of taking band aid approach - asking for denormalizing the db tables with so called WORK TABLES - It is serving two purposes, saving them from coding nightmares and improving apps performance. As far as RI is concerned I have seen in places they do not use RI rather loves to maintain data integrity by using triggers. My opinion on this - This is more of a preference than to do with any technicality. I am for RI. Shaibal From: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Database Normalization-Outdated? Date: Wed, 01 May 2002 04:03:29 -0800 _ 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: Shaibal Talukder INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Database Normalization-Outdated?
To speak to that point; we are the state tax department. We received and send mail, hundreds of pieces. Our original tax appliation required us to go to at least10 tables to get an address because it was completely normalized. When we go the new and improved version this was denormalized for the sake of design and speed. Storage is cheap now, but bad coding can take more resources than you have. Just my $.02, Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, May 01, 2002 12:08 PM List- My Opinion: The purpose of Normalization is to reduce redundant storage. It is a trade off between affording redundancy(as far as you can?)and avoiding to many joins later. It can not be true accross the board. Designers should go by the merit/requirements of the application. If database is normalized as and where it is possible(i.e. up to Boyce-Codd Normal Form); it might become a nightmare while trying to run BUSINESS OBJECTS/COGNOS/etc against it. As Hanna just mentioned joining 17 tables. AT my present work place I am dealing with a db which is so poorly designed. Now to be able to support BUSINESS OBJECT the developers are kind of taking band aid approach - asking for denormalizing the db tables with so called WORK TABLES - It is serving two purposes, saving them from coding nightmares and improving apps performance. As far as RI is concerned I have seen in places they do not use RI rather loves to maintain data integrity by using triggers. My opinion on this - This is more of a preference than to do with any technicality. I am for RI. Shaibal From: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Database Normalization-Outdated? Date: Wed, 01 May 2002 04:03:29 -0800 _ 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: Shaibal Talukder INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Ruth Gramolini INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Database Normalization-Outdated?
I agree whole-heartedly. Besides, what that consultant is describing is just the logical view of x.500, LDAP or something like it. Its how you logically view that type of database. If you look at most LDAP products, the internal structure of the database is relational and highly normalized. That logical view is presented through rigid RI, triggers and stored procedures to make data retrieval very fast and simple for a very specific type of application. -Original Message- Sent: Wednesday, May 01, 2002 12:08 PM To: Multiple recipients of list ORACLE-L List- My Opinion: The purpose of Normalization is to reduce redundant storage. It is a trade off between affording redundancy(as far as you can?)and avoiding to many joins later. It can not be true accross the board. Designers should go by the merit/requirements of the application. If database is normalized as and where it is possible(i.e. up to Boyce-Codd Normal Form); it might become a nightmare while trying to run BUSINESS OBJECTS/COGNOS/etc against it. As Hanna just mentioned joining 17 tables. AT my present work place I am dealing with a db which is so poorly designed. Now to be able to support BUSINESS OBJECT the developers are kind of taking band aid approach - asking for denormalizing the db tables with so called WORK TABLES - It is serving two purposes, saving them from coding nightmares and improving apps performance. As far as RI is concerned I have seen in places they do not use RI rather loves to maintain data integrity by using triggers. My opinion on this - This is more of a preference than to do with any technicality. I am for RI. Shaibal From: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Database Normalization-Outdated? Date: Wed, 01 May 2002 04:03:29 -0800 _ 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: Shaibal Talukder INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Seefelt, Beth INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Database Normalization-Outdated?
All, Might as well add my 2 cents. I say Normalize as we are supposed to, and not worry about designing an OLTP database for use by the latest query/reporting tools. Lets face it, COGNOS and the rest just plain do not work against OLTP databases anyway. We end up either designing and implementing a warehouse or snapshot/materialized/views/summary tables for these products anyway. So why not keep the OLTP system designed as it should be. At least we will have a tight-nicely designed system where all of the rules are followed. again, my opinion. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, May 01, 2002 12:08 PM To: Multiple recipients of list ORACLE-L List- My Opinion: The purpose of Normalization is to reduce redundant storage. It is a trade off between affording redundancy(as far as you can?)and avoiding to many joins later. It can not be true accross the board. Designers should go by the merit/requirements of the application. If database is normalized as and where it is possible(i.e. up to Boyce-Codd Normal Form); it might become a nightmare while trying to run BUSINESS OBJECTS/COGNOS/etc against it. As Hanna just mentioned joining 17 tables. AT my present work place I am dealing with a db which is so poorly designed. Now to be able to support BUSINESS OBJECT the developers are kind of taking band aid approach - asking for denormalizing the db tables with so called WORK TABLES - It is serving two purposes, saving them from coding nightmares and improving apps performance. As far as RI is concerned I have seen in places they do not use RI rather loves to maintain data integrity by using triggers. My opinion on this - This is more of a preference than to do with any technicality. I am for RI. Shaibal From: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Database Normalization-Outdated? Date: Wed, 01 May 2002 04:03:29 -0800 _ 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: Shaibal Talukder INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Mercadante, Thomas F INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Database Normalization-Outdated?
Normalization process should be done at the logical level, but it can be denormalized at the physical level to support must have transaction processings. -Original Message- Sent: Wednesday, May 01, 2002 9:48 AM To: Multiple recipients of list ORACLE-L To speak to that point; we are the state tax department. We received and send mail, hundreds of pieces. Our original tax appliation required us to go to at least10 tables to get an address because it was completely normalized. When we go the new and improved version this was denormalized for the sake of design and speed. Storage is cheap now, but bad coding can take more resources than you have. Just my $.02, Ruth - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, May 01, 2002 12:08 PM List- My Opinion: The purpose of Normalization is to reduce redundant storage. It is a trade off between affording redundancy(as far as you can?)and avoiding to many joins later. It can not be true accross the board. Designers should go by the merit/requirements of the application. If database is normalized as and where it is possible(i.e. up to Boyce-Codd Normal Form); it might become a nightmare while trying to run BUSINESS OBJECTS/COGNOS/etc against it. As Hanna just mentioned joining 17 tables. AT my present work place I am dealing with a db which is so poorly designed. Now to be able to support BUSINESS OBJECT the developers are kind of taking band aid approach - asking for denormalizing the db tables with so called WORK TABLES - It is serving two purposes, saving them from coding nightmares and improving apps performance. As far as RI is concerned I have seen in places they do not use RI rather loves to maintain data integrity by using triggers. My opinion on this - This is more of a preference than to do with any technicality. I am for RI. Shaibal From: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Database Normalization-Outdated? Date: Wed, 01 May 2002 04:03:29 -0800 _ 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: Shaibal Talukder INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Ruth Gramolini INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Wong, Bing INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Database Normalization-Outdated?
Lisa, Any chance of getting the name of both the consultant and the book? Jared Lisa R. Clary [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/30/2002 12:48 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Database Normalization-Outdated? 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
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 [EMAIL PROTECTED] 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: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public
Database Normalization-Outdated?
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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
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 [EMAIL PROTECTED] Reply-To : [EMAIL PROTECTED] To : Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Database Normalization-Outdated?
Lisa - not sure i follow the specific example, but i say RI is still a wonderful feature of an RDBMS and should not be thrown out. Many package apps have not implemented RI because they run on many RDBMS platforms, and each handles RI differently. So put the RI in the app they say - right! Not fun when the app forgets. I've seen some real messed up PeopleSoft data. Codd was a smart guy. I'd stick to my guns if i were you. Don't have any bleeding edge white papers to support the argument, but we're building a new app with cool tools (java, app servers, oracle db). Guess what - the Db is still normalized. Then again, maybe I'm just an old curmudgeon - i am nearly 40 now. -Original Message- Sent: Tuesday, April 30, 2002 2:49 PM To: Multiple recipients of list ORACLE-L 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: STEVE OLLIG INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Database Normalization-Outdated?
If this concept is so simple (simplistic?), why does it take a whole book to elaborate on it? Lisa R. Clary wrote: [...snip...] 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Database Normalization-Outdated?
Too funny! The first problem that comes to mind is duhvelopers: There are a lot of developers that would simply be incapable of dealing with Meta Data for every day queries. We tried to use some very flexible meta data in a DW, and it turned out to be too hard for the developers to grasp, many of whom were quite good. So all data administration will come down to maintaining event codes in a single table. I know a few DA's that would likely disagree with this. :) This is a damagers dream: buy a SAN with one filesystem striped across the whole box, build one table to hold all of the data. PHB's will love it. DBA's? What DBA's? We don't *need* no *stinking* DBA's! May we have the consultants name and the Title of the book? It would be much easier to shoot this full of holes on a point by point basis. Jared Lisa R. Clary [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/30/2002 12:48 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Database Normalization-Outdated? 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
