Are the posted questions getting worse?
-
L' Eomot Inversé (11/18/2012)
Jeff Moden (11/17/2012)
Evil Kraig F (11/14/2012)
Oh... EAV model... HOW I HATE YOU......
....
I've run into that same kind of garbage before. In fact, I worked with several different sets of front-end "developers" and they all had the same basic misunderstanding. That horrific execution plan isn't necessarily because of EAVs even if they are present. More likely, it's because the "developers" just didn't know how to use them correctly. Considering that the plan you posted seems to be one of those bloody "all-in-one" "aggressive-get" queries that some front-end "developers" seem so fond of, they also seem to be following the same path of the "developers" I worked with... they knew nothing of "Divide'n'Conquer" methods and let things like the "Design Studio" and the Orms do all the work for them. That also why every table they "built" had all NVARCHAR(4000) columns even if a column would only ever hold a single character no matter how many columns the table had.
I agree that EAVs shouldn't be used if something better is available but I believe that such "developers" have given them much more of a bad name than they deserve.
I agree that EAVs can be appropriate for some tasks in some circumstances. But those circumstances have to include the availability of a develoment team competent at handling that data model, and "development team" usually means the app development people as well as database developers, because it's usually app or gui deveoplers who promote an EAV model.
Although I agree with Jeff that there are circumstances in which an EAV model is the best option, I'm inclined to share Craig's hate of EAV, for two reasons.
1. I have a strong belief in modularity of design, and that the data layer, the application layer, and the presentation layer should be separated by well-defined interfaces that are as narrow as possible; so I hate the idea of the application layer being able to see the schema, and want everything in the databse hidden by an API which is presented as a set of procedures, or a set of abstract data objects seen through their methods *which are, or course, stored procedures). At a conceptual level this is easy to do in something like SQL Server, Ingres, or Postgres, Oracle, or DB2, although the practical difficulty of doing this depends on how good the databases documentation is and how good support is, so it varies a lot between those 5 DBMSs. Normally an EAV leads to total visibility of the schema in the application layer and complete abandonment of any attempt at modularity; this of course is not an automatic consequence of an EAV model, but since it's usually the app developers (and/or the end-user interface designers) that push for the EAV model it is certainly the norm for EAV models.
2. I believe in normalisation. If every functional dependency and every multi-valued dependency which is not a functional dependency is enforced by the schema, it's impossible to write any code (whether in the database or in the app) which will cause those dependencies not to be satisfied. That eliminates all sorts of data corruption issues and leads to early detection (normally during unit testing) or coding errors. The normalisation can be taken further, and address join dependencies too if necessary. But an EAV mode can't even be reduced to first normal form, since the data domains are not reflected in the data structure. Even if that could be got around, the data probably couldn't be put into 2NF, 3NF, EKNGF, BCNF, 4NF, or 5NF because the table decomposition which is used to generate these normal forms means that the data ceases to be EAV. All functional, multivalued, and join dependencies have to be enforced by application code when an EAV model is used and exposed to the application, and that is generally a punitive development cost and a disastrous support and maintenance cost.
So in my view, only in the rare cases where either (1) both the inability to normalise and the absence of a modular separation of data stucture in long term storage from the application code is acceptable will an EAV solution with the EAV model visible to the application code be viable or(2) the EAV model is purely internal to the database which presents an abstract data API to the app and the database people have determined that the loss of normalisation is acceptable in exchange for some additional flexibility is an invisible EAV model viable. I believe that case(1) is extremely rare, so rare that most people will never encounter this case, and that while case (2) is less rare than case (1) it is still sufficently rare that most will never encounter it, and most of those who do will encounter it only in circumstances where the EAV model is is used only for a small subset of the data rather than for all of it. I also believe that most people who encounter an EAV model built in an RDBMS will not encounter case (1) or case(2) but something far more common - an expensive and unsupportable mess built by the sort of developers and ORM-fans that Jeff complained of. But of course my opinion of rareness may be prejudiced by my views on modularity and normalisation
I absolutely agree with everything you said especially the part about the GUI not being able to see the schema information never mind modify it and the developers building "an expensive and unsupportable mess built by the sort of developers and ORM-fans that Jeff complained of".
Unfortunately and right or wrong and as rare as they may be (should be), there are applications that require front-end code to be able to add attributes to an entity and to sometimes even (gack!) add an entity. In either case, the choices narrow down quite a bit, the most common of which seem to be EAVs or XML. To be sure, don't take what I said in my post to Craig to mean than I'm an EAV lover. I am patently not. I share Tom and Craig's hatred for EAVs because, like Tom, I don't believe in the application layer being able to see the schema never mind modify it (well, unless you're rewriting SSMS :hehe:). I do believe, however, that there's a right way and a wrong way to write code against an EAV and that (it seems), few developers (including many backend developers) know correct methods for how to do it. That's what I meant by EAVs getting more of a bad rap than they deserve although I do absolutely agree they should be avoided if they can be avoided.
All of that could make a good argument for XML provided that you've developed a shredder where you don't actually need to know the names stored in tags of the elements you need to be able to interogate (my understanding is that's easily possible in frontend code). But I hate the XML solution nearly as much for the same reasons (and we haven't mentioned them all, of course).
So, without writting code on the thread 😀 and given that we must occasionally accommodate the insanity of users adding user defined attributes to an entity, what would be the best way to allow such a thing to happen on the backend without the app or the user having the privs to actually modify a table? EAV/NVP??? XML??? or ???
Other than initially just flat out refusing to make some accomodations for such user actions, I have no predisposed predjudice in trying to find a better way, either. Open it up folks. Any suggestions?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Steve Jones - SSC Editor (11/18/2012)
Leaving for a week in the mountains. Y'all behave.No can do. We're wired against that.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
Jeff Moden (11/18/2012)
L' Eomot Inversé (11/18/2012)
Jeff Moden (11/17/2012)
Evil Kraig F (11/14/2012)
Oh... EAV model... HOW I HATE YOU......
....
[snip]
[snip]
[snip]
So, without writting code on the thread 😀 and given that we must occasionally accommodate the insanity of users adding user defined attributes to an entity, what would be the best way to allow such a thing to happen on the backend without the app or the user having the privs to actually modify a table? EAV/NVP??? XML??? or ???
Other than initially just flat out refusing to make some accomodations for such user actions, I have no predisposed predjudice in trying to find a better way, either. Open it up folks. Any suggestions?
Just to be clear, I hate EAV and I complain out loud when developers want me to tune their queries against that mess.
That said, the problem exists and there seems to be no simple answer in the relational model. That's one of the points that I can't argue about when somebody advocates NoSQL databases, where the thing seems both possible and encouraged.
It also must be said that many implementations of EAV are possible, with different levels of madness involved.
It goes from a single EAV table for the whole database, linked to all possible entities in the model, to different EAV tables, specialized for a particular entity.
The data type mismatch can be mitigated using different "value" columns for different "attributes", at least for the most common data types (string/date/number). Sparse columns could also mitigate storage consumption issues.
Other implementations I have seen rely on additional (non EAV) tables holding pre-created sets of anonymous columns for a single entity, so that the front-end just needs to save metadata somewhere to map dynamic attributes to anonymous columns of the appropriate data type. Again, this implementation could benefit from the use of sparse columns.
Personally, I hate the XML solution. It's bulky and kills concurrency.
-- Gianluca Sartori
-
WayneS (11/18/2012)
Steve Jones - SSC Editor (11/18/2012)
Leaving for a week in the mountains. Y'all behave.No can do. We're wired against that.
+1
We all know what happens when the teacher isn't in class!
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx -
WayneS (11/18/2012)
Steve Jones - SSC Editor (11/18/2012)
Leaving for a week in the mountains. Y'all behave.No can do. We're wired against that.
He said "behave", he didn't say how to behave ...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
GSquared (11/19/2012)
WayneS (11/18/2012)
Steve Jones - SSC Editor (11/18/2012)
Leaving for a week in the mountains. Y'all behave.No can do. We're wired against that.
He said "behave", he didn't say how to behave ...
In other words - status quo
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
You took all of the joy out of it, Jason. 🙁
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
The Dixie Flatline (11/20/2012)
You took all of the joy out of it, Jason. 🙁Did I?
Are you really sure about that?
What if I gave you Steve's GPS coordinates so you could bug him?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
SQLRNNR (11/20/2012)
The Dixie Flatline (11/20/2012)
You took all of the joy out of it, Jason. 🙁Did I?
Are you really sure about that?
What if I gave you Steve's GPS coordinates so you could bug him?
Me thinks Steve would execte the following
TRUNCATE SQLRNNR
OR DROP SQLRNNR
-
Or maybe just....
EXECUTE SQLRUNNR
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
SQLRNNR (11/21/2012)
Man, where did you get this picture? Bascule is all wrong - it would not swing. And where are the belts to tie the condemned SQL to it?
No, no, no - you will not execute SQL as easily as you think! 🙂
-
Revenant (11/21/2012)
SQLRNNR (11/21/2012)
Man, where did you get this picture? Bascule is all wrong - it would not swing. And where are the belts to tie the condemned SQL to it?
No, no, no - you will not execute SQL as easily as you think! 🙂
I've quit wondering where Jason finds all these pics... me thinks he just spends his whole day looking for these.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
For those that are observing the USA holiday of Thanksgiving - enjoy your day (or 2) off. If traveling, be safe.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
Happy Thanksgiving to all!!!
May the turkey be tender and juicy and the people you're celebrating with be the ones you love and care about. (quote by LutzM)
and lets hope detroit lions will win on this thanksgiving day for once, they haven't won a thng day game since 2003 🙂
-------------------------------------------------------------
"It takes 15 minutes to learn the game and a lifetime to master"
"Share your knowledge. It's a way to achieve immortality."
Viewing 15 posts - 38,296 through 38,310 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply