Attribute-value approach for table design

[Guest]

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 .

 

[Matthias Klaey]

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 .

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

 

[Guest]

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?

 

[Jamie Collins]

I am thinking to put the new fields into a new table, which will has
Attribute Name, TextValue, DateValue, NumberValue, and BooleanValue column.

I don't think you'll find much support for the notorious EAV
(Entity-Attribute-Value) 'design flaw' around here

Jamie.

--

 

[Matthias Klaey]

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?

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

 

[Guest]

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.

 

[Jamie Collins]

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.

Not in my experience. Applications commonly have 'work' tables that are
loaded/cleared as required but they are strongly typed.

Jamie.

--

 

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

 

[Jamie Collins]

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.

The intent of my post was to give a name to the design, using the word
'flaw' in quotes to reflect the low opinion in general application, to
encourage the OP to do some research. Mission accomplished ;-)

Let's face it: the chances of EAV being a newbie error, rather than
considered design choice, are extremely high.

Bear in mind that 'database' and 'SQL DBMS' are different concepts. My
impression from reading around the subject is that if EAV is the
required solution then SQL is not the best means to achieve those ends
i.e. 'performance issues' are a symptom of a fundamental bad fit.

My feeling is that the limited applications that benefit from EAV would
not be suited to an Access/Jet SQL.

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

I've no first hand experience to add.

Jamie.

--

 

[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

 

[Jamie Collins]

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.

--

 

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

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.

 

[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

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/

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.

--

 

[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

 

[Joan Wild]

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

 

[Jeff Boyce]

Gracias!

Jeff

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

 

[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

 

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

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!

 

[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!>.

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.

 

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