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