Re: Attribute-value approach for table design


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've used it Jeff. ... We needed to capture periodic measurements on trees. ... Do you also have any leads on folks who've found the EAV ... "The design flaw you are calling a vertical model is actually known ...
    (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)
  • Re: Another history question was Re: Tunny and SIGABA
    ... Kent Betts wrote: ... > as the same letter was a design flaw, since it was a way into the machine. ... the first letter is neither T nor A nor G, ... constraints and this makes things easier. ...
    (sci.crypt)