Re: Attribute-value approach for table design

I've used it Jeff. We needed to capture periodic measurements on trees. It
was unknown what, how many, or how often attributes would be measured.

Couldn't see modifying the design everytime they measured something, so I
used the EAV model and it worked for our purposes.

I seem to recall reading that David Fenton uses the OTLT approach all the
time.


--
Joan Wild
Microsoft Access MVP

Jeff Boyce wrote:
Jamie

Thanks for the leads, I'll check them out.

I'm concerned about the tone, however, as they seem to carry a common
theme of "stupid, dangerous, newbie, ..." and other words with
derogatory meanings or connotations.

Do you also have any (other) leads on folks who've found the EAV
model to work well?

I'm also concerned by what appears to be an "all-or-none" approach...
either EAV (stupid/wrong) or relational dbms (good, right), with no
mention of when/where the EAV might accomplish something that is
complex, time-consuming, inflexible, etc. for the RDBMS model.

Thanks again!

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


"Jamie Collins" <jamiecollins@xxxxxxxxxx> wrote in message
news:1163496014.457747.147710@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Jeff Boyce wrote:
I understand that some hold EAV in low regard ... I don't find the
reasons why (aside from potential performance). Can you point to
some of the reasons why you consider it a 'flaw'?

I think I see where you are coming from e.g.

"Exploring Performance Issues for a Clinical Database Organized Using
an Entity-Attribute-Value Representation"
http://www.pubmedcentral.nih.gov/articlerender.fcgi?artid=79043

I think everyone agrees query performance is slower with EAV but what
about SQL DDL? Constraints will involve a lots of conditional code
(i.e. using a lot of IIF and SWITCH in Access/Jet terms), which will
make INSERTs and UPDATES slower, and be hard to maintain.

But (and this is a big but) all the above assumes EAV was the correct
design choice.

I think you'll find that EAV is a common newbie error, right up there
with OTLT:

"OTLT and EAV: the two big design mistakes all beginners make"

http://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-big-design-mistakes.html

It's an understandable 'reaction' (what's the opposite of
'epiphany'?) when you're a few hours into you first SQL design
project e.g. Why all this hard work linking[sic] tables where one
would do? Who needs constraints when I have VBA and a rich event
model? I can use autonumber as a generic key[sic], which will
prevent duplicates, right?

As for the coupling of 'EAV' and 'flaw'... well, you know that
Dorothy Parker quip: "I never seek to take the credit | we all
assume Joe Celko said it":

"The design flaw you are calling a vertical model is actually known
as "EAV" or "Entity-Attribute-Value" because it is a common newbie
mistake."

http://groups-beta.google.com/group/comp.databases.ms-sqlserver/msg/a20795c71751de90

"Look up the EAV design flaw you have re-discovered"

http://groups-beta.google.com/group/comp.databases.ms-sqlserver/msg/a497108ef4472742

"It looks like an EAV design flaw, with mixed data and metadata in
the same table."

http://groups-beta.google.com/group/comp.databases.ms-sqlserver/msg/7b6f818709512cf7

"This design flaw is called the EAV model; it fails in one year or
less in production. For fun, try to: 1) Add check constraints and
default; 2) Add a PK-FK constraint; 3) write a query with a simple
GROUP BY clause -- Thomas Coleman posted one of those for an another
EAV -- total nightmare."

http://groups-beta.google.com/group/microsoft.public.sqlserver.programming/msg/24622539e2a544ca

"The name of this design flaw is EAV and there are [lots] of postings
about why it is a stupid, dangerous idea. It comes up over and over
again with newbies -- like Martingales in gambling."

http://groups-beta.google.com/group/microsoft.public.sqlserver.programming/msg/f9f2a172c72be9f3

etc etc

Jamie.

--


.

Relevant Pages

  • Re: Attribute-value approach for table design
    ... I think everyone agrees query performance is slower with EAV but what ... Constraints will involve a lots of conditional code ... the two big design mistakes all beginners make" ... "The design flaw you are calling a vertical model is actually known as ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Attribute-value approach for table design
    ... Do you also have any leads on folks who've found the EAV model to ... I think you'll find that EAV is a common newbie error, ... "The design flaw you are calling a vertical model is actually known as ... about why it is a stupid, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Noob needs polymorphism
    ... >>Don't use an RDBMS. ... This design flaw is called EAV ...
    (comp.databases)
  • Re: Noob needs polymorphism
    ... >Don't use an RDBMS. ... This design flaw is called EAV ... In my address everything before the first period is backwards; ...
    (comp.databases)