Re: Attribute-value approach for table design

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
    ... 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: 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)