Which Oracle book was this?
dba...@yahoo.com
I'm sure it was written by Tom Kyte.
In it, he critiqued a system that would store data in
rows, instead of simple columns!
One criticism being that it performed very badly.
So, instead of a typical table structure:
field1
field2
field3
field4
There was a lookup table with field codes, and what they meant.
Fieldcode_id
Fieldcode_name
The "data" table would be structured:
Fieldcode_id
value
etc.
Doing simple queries was of course much more complex.
I remember reading words to the effect:
"How did it perform? Horribly, pathetically, awfully!"
I'm unfortunately dealing with such a system now,
and would like to show this critique to the brass.
Which book can I find this in please?
Thanks a lot!
gym dot scuba dot kennedy at gmail
<dba...@yahoo.com> wrote in message
news:bca7bad6-febc-429d...@u65g2000hsc.googlegroups.com...
You can go to Tom Kyte's site (asktom) Also there is a story in Tales from
the Oak Table about this and what a horror it was. Alluring, but a costly
horror.
Jim
David Portas
See also:
http://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-big-design-mistakes.html
--
David Portas
Michael Austin
I was aware of at least one system - for a name-brand pharmaceutical
company that used this design. How did this one perform? It was
running DEC now Oracle/Rdb (OpenVMS) and when tuned properly really
screamed. Yes, the queries were very ugly, but when properly configured
- did very well.
One benefit was it flexibility in self-describing the contents. When I
first arrived at the site and saw this design, I was very skeptical as
to how it would perform. Using MIXED Storage areas (loosely analogous
to a IOT where index and the data for that index reside in the same data
page (extent in Oracle terms)) performance was more than adequate.
Tim X
I'm not sure which of Tom's books it was, but you are likely to find
something on his site asktom.oracle.com or you could even post a
question there and he may respond (in time - he gets lots of questions).
I agree with the argument against attribute-value style tables. However,
I do believe they can have a use and that it isn't an absolute 'never do
it' situation.
I'm a very strong proponent of putting as much integrity constraint into
the database as possible. All types should match as cloesly as possible
the type of the data (e.g. do not use varchar2(4000) for all varcchar2
fields, use not null if it should never be null, use foreign keys
wherever possible, use check constraints where appropriate, use date
fields for dates etc. I constantly have arguements with other developers
who argue this takes away from the flexibility of the design. usually, I
argue that if you find your design isn't flexible enough, its because
your analysis wasn't good enough in the first place.
Currently, I'm developing a replacement system for one that was poorly
designed and where they have used attribute-value style tables. Its a
mess and has been a problem for a long time. The new design has
exttensive constraints and a lot of tables that only hold few rows of
data.
However, I have got one attribute-value style table. The table is a
configuration table that is used by the application to set certain
behavior. It only has about 5 rows of data and is unlikely to have more
than 10. It is not part of the main data model of the application and
isn't used in the main select, update or insert operations. It does things like
hold the e-mail address of who error reports are sent to, identifies if
the database is a development, QA or production system (which controls
things like where e-mail notifications are sent - to clients based on
data in the client table or to the testing team or development team
etc (to some extent, I expect newer Oracle features, such as conditional
compilation and other features can now satisfy some of these
requirements - just have to find the time to look into such stuff!)
For this type of use, I think an attribute-value table is OK. In general
though, it should be avoided.
Tim
--
tcross (at) rapttech dot com dot au
dba...@yahoo.com
I'm sure it was written by Tom Kyte.
In it, he critiqued a system that would store data in
rows, instead of simple columns! And, it performed very badly.
So, instead of a typical table structure:
field1
field2
field3
field4
There was a lookup table with field codes, and what they meant.
Fieldcode_id
Fieldcode_name
The "data" table would be structured:
Fieldcode_id
value
etc.
hpuxrac
wrote:
This just doesn't sound right.
> One benefit was it flexibility in self-describing the contents. When I
> first arrived at the site and saw this design, I was very skeptical as
> to how it would perform. Using MIXED Storage areas (loosely analogous
> to a IOT where index and the data for that index reside in the same data
> page (extent in Oracle terms)) performance was more than adequate.
Reasonable designs for a relational database are done using ERD's and
3nf.
DA Morgan
Flat files are fast.
Relational designs promote data integrity.
There's a reason no one uses 5NF.
We compromise in the middle where we obtain adequate data integrity
with adequate performance.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Palooka
>
> Reasonable designs for a relational database are done using ERD's and
> 3nf.
Agreed in priciple, as a logical design. But when transposing the
logical design into a physical one, it might be sensible to normalise
further in some selected areas for application flexibility, and/or to
denormalise (carefully) in others for performance. IMHO though, each
such change should be considered carefully, and a justification documented.
Just my 2c.
Palooka
Helma
you could ask Tom himself? And let the brass read the article in the
'Oracle Insights - tales of the Oak Table' , which is on url:
www.simple-talk.com/opinion/opinion-pieces/bad-carma/
a large read, but part of every Oracle DBA's education.
Mark D Powell
On Page 34 of Effective Oracle by Design there is a warning about
using generic data models. Maybe this is the book you are thinking
of.
HTH -- Mark D Powell --
robert
> See also:
> http://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-big-design-mistakes.html
>
Nice reference. It is amazing how often the EAV anti-pattern pops up in
real life. At least twice a few years ago, I'd been unsuccessful
arguing it down, because someone in management was so enamored of the idea.