Attribute-value approach for table design

[Jamie Collins]

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!

The tone is Celko's, not mine I hope <g>.

What *I* have tried to say is that EAV has its place, albeit in a
limited number of applications which are well documented.

Personally, I find the attribute-centric approach more interesting. If
you've read more than a couple of my posts you'll know I'm particularly
keen on writing as many database constraints as I possibly can to
maintain data integrity. I am very wary of the reliance on front end
applications or even business rules engines to maintain data integrity.

I've worked on a clinical DBMS, being a hospital prescribing
application, and I can tell you that EAV has no place in electronic
prescribing, something described as a 'life critical' system (I had to
have multimillion dollar insurance against someone suing me _in my own
right_). Oh yes, we had our constraints close to the data and in the
database. I saw someone's sig line yesterday: "A front-end is something
that tries to violate a back-end"; I couldn't have put it better myself
(apologies if that quote sounds smutty <g>)

New drugs are being made available all the time, so that part of the
database plus the data updates was purchased from a trusted source. Our
application used industry standard keys throughout, all maintained by
trusted sources (e.g. the British National Formulary). There was one
artificial key in the whole app and that was merely a staging table
while duplicates in externally generated episode IDs were resolved.

I trust you understand why you will not get the pros of EAV out of me:
I'm a database constraints evangelist

I've also tried to convey that I don't see EAV as 'just wrong' and I'm
happy to repeat that message. It's just I usually see EAV used where an
attribute-centric approach would appear to me to be more appropriate.
To employ the usual analogy, sometimes a hammer is the correct tool but
often one is misused to bash in screws, often by people who are
"Unskilled and Unaware of It" (google it) which makes them potentially
dangerous.

There's a guy sitting right behind me (I'll just reduce the zoom in my
word processor so he can't read over my shoulder <g>), sole developer
on an in-house app. The product manager got suspicious when seemingly
fundamental amendments to the schema were implemented very quickly.
When he took a look at the database he found - you've guessed it - one
table, two stored procs (a Get and a Set), no business/enterprise keys
(GUID INDENTITY PK), no constraints, etc. The developer is from an OOP
background and these guys tend to view the database as a dumb store for
their business rules engines and so EAV makes perfect sense. The
product manager was not best pleased, though, because he was planning a
second app to be based on the same database but immediately realised
that he had no schema and all the business rules (defaults, domain
values, etc) were locked in ASP.NET code.

Jamie.

--

 

[Jamie Collins]

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, I appreciate your honesty, I really do, but as usual I find
myself in total disagreement.

How can you have a concept of 'best practice' or hope to learn from the
mistakes of others? I was taught that the professional does thinks of
others, especially the poor so-and-so that will have to maintain/port
their code after they're gone.

I find it particularly cynical to write code you know to be a
"nightmare of inconsistency". I only hope your words inspire others to
think, "I can be so much better than that."

Jamie.

--

 

[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

 

[Joan Wild]

I do?

I don't even know what the acronyms mean!

But I'm pretty lost terminology-wise!

I only learned the acronym yesterday

 

[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

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

[...]

 

[Jamie Collins]

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.

I'm not sure I can agree about "gray".

I've seen/heard of EAV solutions which would seem to be viable
candidates for attribute-centric solutions. Saying an attribute-centric
solution (where available) is better than the equivalent EAV solution
is a subjective statement but there is no "gray" in my mind (but I
really don't know: there may be some "gray" and I'm just not seeing
it).

I guess I'm saying I see EAV as a 'design of last resort' as regards
using a SQL product. And yes, when applicable I'd exect to see a mix of
attibute-centric (major) and EAV (minor).

BTW there's a related thread on sqserver.prgramming just now:

http://groups-beta.google.com/group/microsoft.public.sqlserver.programming/msg/2ea56b75507d4900

Bad example; you would use the industry standard UPC/EAN/GTIN codes on
the cans as the natural key for the Inventory table. Each manufacturer
will have his own UPC number, within which we would find various peas
that differ by type of peas and the size of the can... Another example
was a posting a few years back for a record collection in an EAV. The
poster had not heard of the Schwann catalog or RIAA and was inventing
his own system on the fly.
[unquote]

A good example where someone thought EAV was the answer whereas better
research of existing solutions was required.

Jamie.

--

 

[David W. Fenton]

I only learned the acronym yesterday

So, I'm only a day behind you? Cool! I'm catching up!