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 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 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: Tim Gorman > 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). === message truncated === __________________________________________________ Do You Yahoo!? Yahoo! Health - your guide to health and wellness http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Pass 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).
