| Author |
Topic |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2005-11-03 : 12:07:17
|
| Anybody have any experience of EAV databases? see [url]http://www.jmir.org/2003/4/e27/[/url] I'd be interested to know the pro's and con's apart from what is suggested in the article.steve-----------Facts are meaningless. You could use facts to prove anything that's even remotely true! |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-11-03 : 14:05:01
|
| I've seen that model applied to web metric software....what a nightmare.....think it's bad enough to join to another table...think of this as joining to itself for every require column, with infered entity relationships to boot.I mean it would be a good idea to store meta data about the relational model as well.....Holy dynamic sql batmanBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
 |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-11-04 : 03:02:51
|
| DO you think Yukon would handle this sort of stuff better?steve-----------Facts are meaningless. You could use facts to prove anything that's even remotely true! |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-11-04 : 03:35:50
|
| The primary problem with this "model" is type support. And it is the least understood. As witnessed by the article referenced. It almost brushes past the issue entirely.If you can tame the types, you might have a slim chance...DavidMA front-end is something that tries to violate a back-end. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-11-04 : 09:16:00
|
| Steve,What's the underlying issue here?Are you being asked to implement such a model, or they asking you for your advice.And what's the practical application for this model (ie what are you trying to do?)Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-11-04 : 10:17:45
|
| I worked on a system where someone actually implemented this. It was a gigantic mess. The problem is that relational database engines are just not designed to work with this model, so instead of having the data model stored in the physical table structure, all knowledge about the data ends up being stored in the application.Also, the queries become nightmares of inefficiency. Think about scanning a 40 million row table to do a zip code validation. Now do the same, only join it to itself 15 times for a variety of different attributes.Basically, this approach takes the hard job of constantly updating a rapidly changing data model and turns it into something a hundred times worse.CODO ERGO SUM |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-11-04 : 10:53:24
|
| To be honest it's interest more than anything. I have an interview next week for a job that involves clinical trials and I stumbled across this and then I wondered if anyone was really using it. I can't help but wonder if a datawarehouse type approach would be more suitablesteve-----------Facts are meaningless. You could use facts to prove anything that's even remotely true! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-11-04 : 11:21:06
|
| I think the whole approach is similar to this idea:Instead of keeping 20,000 parts in inventory to make our products, lets just keep elements in inventory. They can be used to make anything.Mechanic: I need 100 sheet metal screws.Inventory Guy: Here's some iron, carbon, and chrome.Mechanic: Huh???Inventory Guy: That's everything you need to make them. We're saving a bundle on inventory costs!CODO ERGO SUM |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-11-04 : 11:27:11
|
quote:
Originally posted by elwoos
To be honest it's interest more than anything. I have an interview next week for a job that involves clinical trials and I stumbled across this and then I wondered if anyone was really using it. I can't help but wonder if a datawarehouse type approach would be more suitable
But what's the position? It's good you're doing research, but just go in with what you know....I always called them Vertical tables....but I don't think that's a real term...it's just a good visual.....Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2005-11-04 : 12:32:14
|
| I wonder how they get decent performance out of RDF triple stores? *Presumably not by basing them on a few EAV-style tables in a relational database.* Hmm, perhaps that's begging the question. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-11-04 : 16:01:15
|
| To my own horror, I found that the my current project required a EAV type solution for a subset of the schema.The conversation with the client was good fun...Client: We need to be able to add any kind of data we want on the fly.Me: This data is not important to you?Client: Pardon?Me: Why don't you know what kind of data it is today?Client: Because my protocols might change in the future.Me: I could remodel and upgrade as that happens.Client: Not good enough!Me: Ok then. I will try, but I will not be held responsibility for performance, integrity or business decisions based on this data.Client: Fine.Me: Excellent. I suggest you start reading come data management books.Client: Why?Me: Because you have just become a DBA.DavidMA front-end is something that tries to violate a back-end. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2005-11-04 : 17:00:25
|
| I have done an EAV database design before. The big advantage is that much of your logical model is stored as data rather than as schema, so changes to the logical model can be made without changing the schema. And if you write your sprocs correctly, you won't need to change them either.The drawback? VERY complicated SQL code. The main reason I used this was for a client that did not have a clear understanding of their own requirements, but needed a working application on a deadline. The EAV schema allowed me to deal with constantly redefined business rules and requirements. Phase two of the project would flatten out the schema, assuming most of the discovery was accomplished in phase 1.I would not shirk from using EAV again, but project managers balk at it because the cannot understand the concept or the read the SQL code to support it. This always ticks me off, because it's like a project manager telling a developer to code in BASIC using GOTO statements because that is all he can understand. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-11-04 : 20:13:04
|
| Of the stuff written about it and in use, I have only ever seen a "self join" solution for multiple attribute comparison. It has always puzzled me because that type of query basically translates to a division operation within a EVA schema.There is a solution that requires no self join or dynamic sql to answer multiple attribute equality quieries. It just requires preparing a Attribute/Value dividend table.. I'm currently using it and it's holding up ok in initial testing.DavidMA front-end is something that tries to violate a back-end. |
|
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2005-11-04 : 23:43:29
|
| I bet Fabian Pascal and Chris Date at http://www.dbdebunk.com would have a field day ripping this article to pieces. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2005-11-05 : 21:50:15
|
| Oh, I truly hope so. Anything to bump "blindman" on higher on Google results. There is no such thing as bad publicity, right? |
|
|
|
limeyredneck
Starting Member
1 Post |
Posted - 2005-12-02 : 08:10:31
|
| That article pretty much answers the main questions about pros and cons.I've been doing some research myself and the only areas I can see where an EAV is the preferred model comes down to two: 1/ Regular and extensive entity, attribute, and value changes such as in a clinical test database or a parts supplier2/ Source form data repository (meaning as a new domain is required new tables reflecting the source are added and yes this does happen all too frequently).(there is a third, an employer thats too cheap and impatient to build a decent unified model initially)There are some ways of optimizing the model, effectively adding a layer over the "core" model - drop your Codd and Date mindset and you'll figure a few out pretty quickly. It comes down to your comfort level with operational complexity in a business logic layer between the user and the data. |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-12-02 : 08:28:18
|
| The job I applied for did involve Clinical Trials but I didn't get it anywaysteve-----------Facts are meaningless. You could use facts to prove anything that's even remotely true! |
|
|
|
|
EAV?