If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Attribute-value approach for table design
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! 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. -- |
#22
|
|||
|
|||
Attribute-value approach for table design
David W. Fenton wrote: 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, 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. -- |
#23
|
|||
|
|||
Attribute-value approach for table design
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 "Jamie Collins" wrote in message oups.com... 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! 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. -- |
#24
|
|||
|
|||
Attribute-value approach for table design
David W. Fenton wrote: "Joan Wild" wrote in : 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! But I'm pretty lost terminology-wise! I only learned the acronym yesterday :-) -- Joan Wild |
#25
|
|||
|
|||
Attribute-value approach for table design
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" -DISCARD_HYPHEN_TO_END wrote: 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 [...] |
#26
|
|||
|
|||
Attribute-value approach for table design
Jeff Boyce wrote: 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/...a56b75507d4900 [quote] Row-modeled EAV is used in EVERY database that must accommodate new brands and cans of peas in Product tables. 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. -- |
#27
|
|||
|
|||
Attribute-value approach for table design
"Joan Wild" wrote in
: David W. Fenton wrote: "Joan Wild" wrote in : 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! But I'm pretty lost terminology-wise! I only learned the acronym yesterday :-) So, I'm only a day behind you? Cool! I'm catching up! -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
Thread Tools | |
Display Modes | |
|
|