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/art...gi?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...-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/...0795c71751de90

"Look up the EAV design flaw you have re-discovered"
http://groups-beta.google.com/group/...97108ef4472742

"It looks like an EAV design flaw, with mixed data and metadata in the
same table."
http://groups-beta.google.com/group/...6f818709512cf7

"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/...622539e2a544ca

"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/...f2a172c72be9f3

etc etc

Jamie.

--

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" wrote in message
oups.com...

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/art...gi?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...-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/...0795c71751de90

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

http://groups-beta.google.com/group/...97108ef4472742

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

http://groups-beta.google.com/group/...6f818709512cf7

"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/...622539e2a544ca

"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/...f2a172c72be9f3

etc etc

Jamie.

--

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" wrote in message
oups.com...

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/art...gi?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...-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/...0795c71751de90

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

http://groups-beta.google.com/group/...97108ef4472742

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

http://groups-beta.google.com/group/...6f818709512cf7

"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/...622539e2a544ca

"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/...f2a172c72be9f3

etc etc

Jamie.

--

Thanks, Joan.

I've found that a single EAV-like table provides a convenient place for
me-as-developer (and not for user-use) to manage "settings" (yes, I know I
could create an INIT text file, but that would be an outside piece, not part
of a self-contained .mdb).

I'm trying to see if Jamie (or others) can offer both pros and cons for use
of the EAV model, but so far, it seems like the arguments against it are 1)
bad behavior/performance, and 2) it's just wrong.

I've not been seeing a discussion of the circumstances in which it might
work, so thanks for something to the positive.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Joan Wild" wrote in message
...
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:
Thanks, Joan.

I've found that a single EAV-like table provides a convenient place
for me-as-developer (and not for user-use) to manage "settings" (yes,
I know I could create an INIT text file, but that would be an outside
piece, not part of a self-contained .mdb).

I'm trying to see if Jamie (or others) can offer both pros and cons
for use of the EAV model, but so far, it seems like the arguments
against it are 1) bad behavior/performance, and 2) it's just wrong.

I've not been seeing a discussion of the circumstances in which it
might work, so thanks for something to the positive.

Here's David's take on it:
http://dfenton.com/DFA/download/Access/LookupAdmin.html


--
Joan Wild
Microsoft Access MVP

Gracias!

Jeff

"Joan Wild" wrote in message
...
Jeff Boyce wrote:
Thanks, Joan.

I've found that a single EAV-like table provides a convenient place
for me-as-developer (and not for user-use) to manage "settings" (yes,
I know I could create an INIT text file, but that would be an outside
piece, not part of a self-contained .mdb).

I'm trying to see if Jamie (or others) can offer both pros and cons
for use of the EAV model, but so far, it seems like the arguments
against it are 1) bad behavior/performance, and 2) it's just wrong.

I've not been seeing a discussion of the circumstances in which it
might work, so thanks for something to the positive.

Here's David's take on it:
http://dfenton.com/DFA/download/Access/LookupAdmin.html


--
Joan Wild
Microsoft Access MVP

Joan

What I see in a cursory look-through is what Jamie (and his referrents) are
seemingly denigrating, the use of a single lookup table to replace several
small "code" tables.

Perhaps I'm seeing a "gray", where others are seeing black or white. The
concept of an EAV appears to be considered bad in all settings, for all
purposes, but it sounds like you and I find value in the use of a single EAV
table for a narrow purpose. I guess I can better understand the concerns
about the OTLT approach, if ALL lookup tables were mushed together, but
David points out that there are limits to the usefulness.

It's just that I haven't been able to determine yet whether this is a topic
that I should file under "best practices" or under "religious wars" g!.

Thanks again.

Jeff

"Joan Wild" wrote in message
...
Jeff Boyce wrote:
Thanks, Joan.

I've found that a single EAV-like table provides a convenient place
for me-as-developer (and not for user-use) to manage "settings" (yes,
I know I could create an INIT text file, but that would be an outside
piece, not part of a self-contained .mdb).

I'm trying to see if Jamie (or others) can offer both pros and cons
for use of the EAV model, but so far, it seems like the arguments
against it are 1) bad behavior/performance, and 2) it's just wrong.

I've not been seeing a discussion of the circumstances in which it
might work, so thanks for something to the positive.

Here's David's take on it:
http://dfenton.com/DFA/download/Access/LookupAdmin.html


--
Joan Wild
Microsoft Access MVP

"Joan Wild" wrote in
:

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.

I do?

I don't even know what the acronyms mean!

If you mean this:

http://www.dfenton.com/DFA/download/...okupAdmin.html

then I guess I *do* use it all the time.

But I'm pretty lost terminology-wise!

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"Jeff Boyce" wrote in
:

It's just that I haven't been able to determine yet whether this
is a topic that I should file under "best practices" or under
"religious wars" g!.

Why worry about what other people think?

Try it and if you can make it work for you and it saves time, then
use it.

I find it extremely easy to drop the lookup table and form into new
apps and then I often write queries return the individual lookup
types. I even do the sacreligious and name the queries things like
tblMyLookupValues, so that in SQL it looks like a dedicated lookup
table. I do this because it makes it easier for me conceptually, but
I can see how it could seem like a nightmare of inconsistency to an
outside programmer try to figure out what the hell was going on.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

David

Thanks for the supporting info.

I'm still learning, so I'm willing to consider other folks' opinions.

If they resort to name-calling, that tells me something useful about their
opinions.

If they reason well, that tells me something too.

--
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/


"David W. Fenton" wrote in message
. 1...
"Jeff Boyce" wrote in
:

It's just that I haven't been able to determine yet whether this
is a topic that I should file under "best practices" or under
"religious wars" g!.

Why worry about what other people think?

Try it and if you can make it work for you and it saves time, then
use it.

I find it extremely easy to drop the lookup table and form into new
apps and then I often write queries return the individual lookup
types. I even do the sacreligious and name the queries things like
tblMyLookupValues, so that in SQL it looks like a dedicated lookup
table. I do this because it makes it easier for me conceptually, but
I can see how it could seem like a nightmare of inconsistency to an
outside programmer try to figure out what the hell was going on.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/