Attribute-value approach for table design

M

Ming

Currently, we have a table, which hold all preferences information of the
company (owner of the application), such as company name, address, phone,
default decimal, tax information. Up to now, this table has one record and 61
columns already. Each time, if the customers want new settings for the
company, we have to add a new field to the table. We also has another table
for application settings as well, which has one record and 44 columns already.

With the new requirement coming, I need to add more settings for the
company. Instead of continuing to add more new fields to the existing table,
I am thinking to put the new fields into a new table, which will has
Attribute Name, TextValue, DateValue, NumberValue, and BooleanValue column.

For example:
AttributeName TextValue DateValue NumberValue BooleanValue
CompanyLogo C:\logo.bmp
CompNameInRpt False
TraceLevel 2

This will give us a flexibility to add any new settings for the company
preferences and for other application settings. I need some advice on this
approach :).
 
M

Matthias Klaey

Ming said:
Currently, we have a table, which hold all preferences information of the
company (owner of the application), such as company name, address, phone,
default decimal, tax information. Up to now, this table has one record and 61
columns already. Each time, if the customers want new settings for the
company, we have to add a new field to the table. We also has another table
for application settings as well, which has one record and 44 columns already.

With the new requirement coming, I need to add more settings for the
company. Instead of continuing to add more new fields to the existing table,
I am thinking to put the new fields into a new table, which will has
Attribute Name, TextValue, DateValue, NumberValue, and BooleanValue column.

For example:
AttributeName TextValue DateValue NumberValue BooleanValue
CompanyLogo C:\logo.bmp
CompNameInRpt False
TraceLevel 2

This will give us a flexibility to add any new settings for the company
preferences and for other application settings. I need some advice on this
approach :).
Click to expand...

I would use three columns

AttributeName (Text)
AttributeValue (Text)
ValueType (Integer)

e.g.

AttributeName AttributeValue ValueType
CompanyLogo C:\logo.bmp 8
CompNameInRpt False 11
TraceLevel 2 2

where ValueType corresponds to the number returned by the VarType
function, see online help. Then using the ValueType, you can convert
the string in AttributeValue to a properly dimensioned variable.


HTH
Matthias Kläy
 
M

Ming

Hello Matthias Klaey,

Thank you so much for your response. Do you think it is good approach to use
attribute-value type table instead of traditional relationtional table in
this case?
 
M

Matthias Klaey

Ming said:
Hello Matthias Klaey,

Thank you so much for your response. Do you think it is good approach to use
attribute-value type table instead of traditional relationtional table in
this case?
Click to expand...

As Jamie Collins remarked, in general the use of the EAV-model ist
very strongly strongly discouraged.
From a practical point of view, what you deal with here is some kind
of "kitchen junk drawer" table that is present in almost all
applications.
Theoretically, you should put every single parameter value in its own
table, but this is clearly a nightmare to manage. However, you will
need to be extra carefull that the integrity of the information in
this table is properly maintained.

Greetings
Matthias Kläy
 
M

Ming

Hello mattias and Jamie,

thank you so much for your valuable advice. I will do some research to see
what I can do with the existing table and new table, mostly likely I will
take your advice and not to use the EAV-model.
 
J

Jeff Boyce

Jamie

Can you offer some pointers to arguments both against and for EAV? A
cursory Google search suggested that there are performance issues in this
design, but that for something like a medical tests database, EAV offers a
practical solution.

Any additional evidence, or are we in the realm of opinion?

Thanks!

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


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

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

Jeff Boyce

Jamie

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'?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
J

Jeff Boyce

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


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

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


Jamie Collins said:
Jeff said:
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'?
Click to expand...

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.
Click to expand...
 
J

Joan Wild

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 said:
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


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

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


Jamie Collins said:
Jeff said:
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'?
Click to expand...

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.

--
Click to expand...
Click to expand...
 
J

Jeff Boyce

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
 
J

Joan Wild

Jeff said:
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.
Click to expand...

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

Jeff Boyce

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
 
D

David W. Fenton

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.
Click to expand...

I do?

I don't even know what the acronyms mean!

If you mean this:

http://www.dfenton.com/DFA/download/Access/LookupAdmin.html

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

But I'm pretty lost terminology-wise! :)
 
D

David W. Fenton

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!>.
Click to expand...

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.
 
J

Jeff Boyce

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


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

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

Jeff Boyce

Jamie

Thanks for the additional clarifications.

I agree, the strong the data is, the more folks who will be able to use it
without resorting to front-end tricks.

And it sounds like you're saying there may be some "gray" ... some
situations in which using a single EAV table (among many well-normalized,
well-constrained tables) could provide a viable solution. I think I had
misunderstood earlier and saw an all/none approach.

Thanks again...

Regards

Jeff
 
M

Matthias Klaey

Jeff

I have on my desk a book from George Koch, "Oracle - the complete
reference", McGraw-Hill 1990, ISBN 0-07-881635-1
http://www.georgekoch.com/OracleFiles/FileIndx.htm
For me, this was one of the first "practical" books on relational
databases.

It contains a very readable introduction to normalization, including a
chapter on denormalization, where he introduces the "kitchen junk
drawer" that I was referring to in my first reply. Basically this is
an EAV table of various items such as configuration parameters for a
specific site that seldom or never change. Access to this table is
granted to only very few people, such as the DBA.

I do use this kind of table in my applications (usually just one),
although in general, I very much agree with Jamie Collins position.

Greetings
Matthias Kläy
--
www.kcc.ch


Jeff Boyce said:
Jamie

Thanks for the additional clarifications.

I agree, the strong the data is, the more folks who will be able to use it
without resorting to front-end tricks.

And it sounds like you're saying there may be some "gray" ... some
situations in which using a single EAV table (among many well-normalized,
well-constrained tables) could provide a viable solution. I think I had
misunderstood earlier and saw an all/none approach.

Thanks again...

Regards
Click to expand...
[...]
 
Top